Very Computer

Hi.

What is the MOST efficient way to round a datetime field BACK to the hour.
For example
select GetDate()
might return 2001-01-17 11:42:01.163.

Now I want to return 2001-01-17 11:00:00.000.

Efficiency is the key word here.  I am using within the Query Analyzer the
following, which works.  But, when doing this to a millions of rows in a
table, the speed is poor.  What would be your suggestions.

select GetDate(),dateadd(hour,DATEPART(hour,
getdate()),convert(datetime,(Convert(varchar,GetDate(),112))))

Thanks,

blue

Unless you create an index on that exact expression your performance will
always be slow since it will have to table scan. If it's something you do
ALL the time it may be worth adding an index, otherwise you will probably
have to live with the performance. The other alternative is to index the
column as  just a datetime and use a between statement with datetime values
that you generate before the select within the proper range.

--
Andrew J. Kelly
Targitmail.com

Quote:> Hi.

> What is the MOST efficient way to round a datetime field BACK to the hour.
> For example
> select GetDate()
> might return 2001-01-17 11:42:01.163.

> Now I want to return 2001-01-17 11:00:00.000.

> Efficiency is the key word here.  I am using within the Query Analyzer the
> following, which works.  But, when doing this to a millions of rows in a
> table, the speed is poor.  What would be your suggestions.

> select GetDate(),dateadd(hour,DATEPART(hour,
> getdate()),convert(datetime,(Convert(varchar,GetDate(),112))))

> Thanks,

> blue

How about

CONVERT(smalldatetime,FLOOR(CONVERT(float,GetDate())*24)/24)
or
CONVERT(smalldatetime,CONVERT(char(14),GetDate(),120)+'00')

Quote:> Hi.

> What is the MOST efficient way to round a datetime field BACK to the hour.
> For example
> select GetDate()
> might return 2001-01-17 11:42:01.163.

> Now I want to return 2001-01-17 11:00:00.000.

> Efficiency is the key word here.  I am using within the Query Analyzer the
> following, which works.  But, when doing this to a millions of rows in a
> table, the speed is poor.  What would be your suggestions.

> select GetDate(),dateadd(hour,DATEPART(hour,
> getdate()),convert(datetime,(Convert(varchar,GetDate(),112))))

> Thanks,

> blue

Quote:>What is the MOST efficient way to round a datetime field BACK to the hour.
>For example
>select GetDate()
>might return 2001-01-17 11:42:01.163.

>Now I want to return 2001-01-17 11:00:00.000.

>Efficiency is the key word here.  I am using within the Query Analyzer the
>following, which works.  But, when doing this to a millions of rows in a
>table, the speed is poor.  What would be your suggestions.

>select GetDate(),dateadd(hour,DATEPART(hour,
>getdate()),convert(datetime,(Convert(varchar,GetDate(),112))))

   SELECT convert(varchar(11), getdate(), 112)

is at least brever. As for effciency, I would like to see the
entire query.

--