Query of set time range relative to current date

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

General SQL Server Forums

New to SQL Server Programming

Query of set time range relative to current date

Author

Topic

nathan323
Starting Member

2 Posts

Posted - 2013-12-26 : 16:32:15

Hi. I come in to work 6.30am, and need to audit what happened from 5pm when I left to 6.30am this morning. I have used code to search 13.5 hours back from any given time:
SELECT * FROM TRANSACTION_HISTORY
WHERE TRANSACTION_HISTORY.ACTIVITY_DATE_TIME > (SELECT DATEADD(hour,-13.5,(SELECT MAX (TRANSACTION_HISTORY.ACTIVITY_DATE_TIME) FROM TRANSACTION_HISTORY)))
Problem is if I run query later, I lose time from the start, eg. If I run query at 7am, I only get results from 5.30pm onwards. Rather than change criteria every day, I wanted to able to search from 6.30am of the current day, back to 5.30pm of the previous day. Can this be done?

nathan323
Starting Member

2 Posts

Posted - 2013-12-26 : 19:42:49

Got it.

SELECT * FROM TRANSACTION_HISTORY th
WHERE th.ACTIVITY_DATE_TIME between cast(cast(getdate() -1 as date) as datetime) + 17.5/24.0 AND cast(cast(getdate() as date) as datetime) + 6.5/24.0;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-27 : 00:55:40

[code]
WHERE th.ACTIVITY_DATE_TIME >= dateadd(day, datediff(day, 0, getdate()), -1) + '17:00'
AND th.ACTIVITY_DATE_TIME < dateadd(day, datediff(day, 0, getdate()), 0) + '06:30'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-28 : 04:44:03

you may just collapse it to this


WHERE th.ACTIVITY_DATE_TIME >= dateadd(day, datediff(day, 1, getdate()), '17:00')
AND   th.ACTIVITY_DATE_TIME <  dateadd(day, datediff(day, 0, getdate()),  '06:30')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources