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.
| Author |
Topic |
|
Goalie35
Yak Posting Veteran
81 Posts |
 Posted - 2010-03-05 : 11:45:52
|
| I need to do a select statement where the date is greater than "2 days prior to the current date, after 2:00PM" but I don't know how to pull this date (other than hard coding it)Something like this. This is based on the query being run on March 5th, 2010:SELECT * FROM Orders WHERE OrderDate >= '03/03/2010 14:00:00'How can I do a subtraction of 2 days and ensure it's after 2:00PM? Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-05 : 11:49:52
|
| OrderDate > DATEADD(hh,14,DATEADD(dd,DATEDIFF(dd,0,GETDATE())-2,0))------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
namman
Constraint Violating Yak Guru
285 Posts |
Posted - 2010-03-05 : 11:54:52
|
| Something like this. This is based on the query being run on March 5th, 2010:SELECT * FROM Orders WHERE OrderDate >= '03/03/2010 14:00:00'what's about this?select dateadd(day, -2, '03/03/2010 14:00:00')select dateadd(day, -2, getdate())SELECT * FROM Orders WHERE OrderDate >= dateadd(day, -2, '03/05/2010 14:00:00') |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-05 : 12:01:45
|
quote:
Originally posted by namman
Something like this. This is based on the query being run on March 5th, 2010:SELECT * FROM Orders WHERE OrderDate >= '03/03/2010 14:00:00'what's about this?select dateadd(day, -2, '03/03/2010 14:00:00')select dateadd(day, -2, getdate())SELECT * FROM Orders WHERE OrderDate >= dateadd(day, -2, '03/05/2010 14:00:00')
when you're using just getdate alone you cant guarantee that time part will be 2 pm always. it depends on actual time when query is run------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
namman
Constraint Violating Yak Guru
285 Posts |
Posted - 2010-03-05 : 13:48:04
|
| you are right, visakh16. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-05 : 13:50:43
|
quote:
Originally posted by namman
you are right, visakh16.
thats why i explicitly added hours to make it 2 pm  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Goalie35
Yak Posting Veteran
81 Posts |
Posted - 2010-03-08 : 10:33:22
|
Thanks for the help guys. The solution worked great!  |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-08 : 10:42:17
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Need to subtract 2 days??