select same time for specific day of week

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

select same time for specific day of week

Author

Topic

agismarkos
Starting Member

17 Posts

Posted - 2012-08-09 : 12:30:59

Hello
I have a table with a column "date" of type smalldatetime and i want to make a select that will return the name of customers that bought product x at every tuesday 3:30 to 4:00 am

thank you very much,
Agis

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-09 : 12:38:19

[code]
SELECT columns...
FROM table
WHERE [date] >=DATEADD(dd,(DATEDIFF(dd,0,getdate())/7 *7)+1,'03:30')
AND [date] <= DATEADD(dd,(DATEDIFF(dd,0,getdate())/7 *7)+1,'04:00')
[/code]

for every week this will make sure it retrieves sales that happened on tuesday of week between given time period

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-09 : 12:50:03

SELECT <columnList>
FROM Table
Where DATEPART(dw,[date]) = 3
AND ((DATEPART(Hh,[date]) = 3 AND DATEPART(mi,[date]) BETWEEN 30 AND 59)
OR (DATEPART(Hh,[date]) = 4 AND DATEPART(mi,[date]) = 0))

-Chad

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-09 : 15:02:59

quote:
Originally posted by chadmat

SELECT <columnList>
FROM Table
Where DATEPART(dw,[date]) = 3
AND ((DATEPART(Hh,[date]) = 3 AND DATEPART(mi,[date]) BETWEEN 30 AND 59)
OR (DATEPART(Hh,[date]) = 4 AND DATEPART(mi,[date]) = 0))

-Chad

not a problem until you've an existing index on [date] column as in that case it wont be able to take advantage of the index due to the NonSARGable predicates used

Also it depends on the DATEFIRST setting in the server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-09 : 15:18:36

quote:
Originally posted by visakh16

quote:
Originally posted by chadmat

SELECT <columnList>
FROM Table
Where DATEPART(dw,[date]) = 3
AND ((DATEPART(Hh,[date]) = 3 AND DATEPART(mi,[date]) BETWEEN 30 AND 59)
OR (DATEPART(Hh,[date]) = 4 AND DATEPART(mi,[date]) = 0))

-Chad

not a problem until you've an existing index on [date] column as in that case it wont be able to take advantage of the index due to the NonSARGable predicates used

Also it depends on the DATEFIRST setting in the server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

This is true.

agismarkos
Starting Member

17 Posts

Posted - 2012-08-10 : 12:42:05

quote:
Originally posted by visakh16

SELECT columns...
FROM table
WHERE [date] >=DATEADD(dd,(DATEDIFF(dd,0,getdate())/7 *7)+1,'03:30')
AND [date] <= DATEADD(dd,(DATEDIFF(dd,0,getdate())/7 *7)+1,'04:00')

for every week this will make sure it retrieves sales that happened on tuesday of week between given time period

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

thank you for your replies!

could you please explain me how these functions work? DATEADD(dd,(DATEDIFF(dd,0,getdate())/7 *7)+1,'03:30')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources