| Author |
 Topic  |
|
|
agismarkos
Starting Member
17 Posts |
 Posted - 08/09/2012 : 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
India
47069 Posts |
Posted - 08/09/2012 : 12:38:19
|
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/
|
 |
|
|
chadmat
The Chadinator
USA
1955 Posts |
Posted - 08/09/2012 : 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
India
47069 Posts |
Posted - 08/09/2012 : 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/
|
Edited by - visakh16 on 08/09/2012 15:04:05 |
|
|
|
chadmat
The Chadinator
USA
1955 Posts |
Posted - 08/09/2012 : 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 - 08/10/2012 : 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')
|
|
|
| |
Topic |
|
|
|
select same time for specific day of week
