Query rows based on time (8am to 8am)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query rows based on time (8am to 8am)

Author

Topic

fiesta_donald
Starting Member

4 Posts

Posted - 2002-02-12 : 14:50:39

I need to select certain rows based on a "datetime" column. I need to select rows from 8am yesterday until 8am today.
In Oracle I would use:
select * from foo where TIMESTAMP >= trunc(sysdate - 1) + 8/24 AND TIMESTAMP < trunc(sysdate) + 8/24.
This would select rows starting at 8am yesterday and ending at 7:59am today.

How would I do this with sqlserver?

thank you,

Mark
Fiesta_donald@email.com

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-02-12 : 15:03:53

when I have a chance I will write the code but it is a combination of

GETDATE() and DATEADD()

there are other ways to do it, like building a character string of yesterdays date with '08:00:00 AM' and another character of todays date with "7:59:59 AM" and then converting those to datetime variables with CONVERT()

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-02-12 : 15:13:59

try this and let me know if it works out for you


SELECT * FROM foo WHERE TIMESTAMP
BETWEEN
     dateadd(hh, 8, dateadd(dd, -1, convert(datetime, convert(char(12), getdate()))))
AND
     dateadd(ms, -3, dateadd(hh, 8, convert(datetime, convert(char(12), getdate()))))

first date parameter for BETWEEN subtracts one day from todays date and adds 8 hours to it.
second date parameter for BETWEEN adds 8 hours to todays date and subtracts 3 miliseconds from it. I tried 1 milisecond and get getting 8 hours. I believe SQL stores dates with a precision of 3 miliseconds so that is why I had to subtract 3

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-02-12 : 15:19:18

instead of


convert(datetime, convert(char(12), getdate()))

you could use


convert(char(10), getdate(), 101)


SELECT * FROM foo WHERE TIMESTAMP
BETWEEN
     dateadd(hh, 8, dateadd(dd, -1, convert(char(10), getdate(), 101)))
AND
     dateadd(ms, -3, dateadd(hh, 8, convert(char(10), getdate(), 101)))

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-12 : 15:28:07

I really think it's better to use >= and < for semi-open intervals, otherwise you're second-guessing the precision of the datatype.

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-02-12 : 15:57:41

column BETWEEN value1 and value2
translates to
column >= value1 and column <= value2.

in this case value1 and value2 become 8:00:00 AM to 7:59:59AM which is why >= and <= will work. If we didn't subtract the 3 miliseconds we would have to use >= and < and not use BETWEEN otherwise 8:00:00 AM for today would be included in the results.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-12 : 16:55:54

Not sure this is better, but it should work:

SELECT * FROM foo WHERE DateDiff(dd, DateAdd(hh, -8, TimeStamp), getdate())=1

This will subtract 8 hours from the timestamp column and compare it to yesterday's date.

Edited by - robvolk on 02/12/2002 16:57:46

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources