date time query [RESOLVED] - SQL Server Forums

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)

date time query [RESOLVED]

Author

Topic

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-10-26 : 10:21:19

Hi,
I need to search a table full of datetime values (actiondate) for the previous week, between monday 6pm and the current monday at 8am.

How can this be done with a WHERE clause.

?

thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-26 : 11:31:55

declare @date datetime

set @date = '10/30/2009'

select datepart(weekday,@date)

SELECT [ThisWeek] = DATEADD(hour,8,DATEADD(week,datediff(week,0,@date),0))
,[LastWeek] = DATEADD(hour,18,DATEADD(week,datediff(week,0,@date)-1,0))

this shows you to make the dates, simply add them to your where clause

WHERE
actiondate between DATEADD(hour,8,DATEADD(week,datediff(week,0,getdate()),0))
and DATEADD(hour,18,DATEADD(week,datediff(week,0,@date)-1,0))

Jim

Everyday I learn something that somebody else already knew

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-10-26 : 11:47:09

Thanks, that sorted it for me.

C

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources