I am looking for a good SQL Statement to select all rows from the previous day from one table. The table holds one datetime column. I am using SQL Server 2005.
A:
Can't test it right now, but:
select * from tablename where date >= dateadd(day, datediff(day, 1, getdate()), 0) and date < dateadd(day, datediff(day, 0, getdate()), 0)
Mark B
2009-10-01 11:16:36
+1
A:
To get the "today" value in SQL:
convert(date, GETDATE())
To get "yesterday":
DATEADD(day, -1, convert(date, GETDATE()))
To get "today minus X days": change the -1 into -X.
So for all yesterday's rows, you get:
select * from tablename
where date >= DATEADD(day, -1, convert(date, GETDATE()))
and date < convert(date, GETDATE())
Konamiman
2009-10-01 11:20:25
"date" Datatype doesnt exist in SQL Server 2005. When i use datetime instead the time value stays and the calculation happens not from 0am to 12pm but from the the time you run the Query
rudimenter
2009-10-01 11:35:32
My mistake. I didn't see you were dealing with SQL Server 2005. Indeed, my code works for SQL Server 2008 only.
Konamiman
2009-10-01 11:50:35
I always thought that DATEADD(day,....) was a waste, just add or subtract the number of days against the date: _SELECT GETDATE()-1_
KM
2009-10-01 11:58:05
A:
Rudimenter,
As well as Mark B's suggestion you can also find more information here:
http://bytes.com/topic/sql-server/answers/673622-need-find-yesterday
- Interestingly, the link leads to a custom function that you can use to save yourself having to duplicate the long code, should you require it in future.
Dal
2009-10-01 11:21:20
+1
A:
get today no time:
SELECT dateadd(day,datediff(day,0,GETDATE()),0)
get yestersday no time:
SELECT dateadd(day,datediff(day,1,GETDATE()),0)
query for all of rows from only yesterday:
select
*
from yourTable
WHERE YourDate >= dateadd(day,datediff(day,1,GETDATE()),0)
AND YourDate < dateadd(day,datediff(day,0,GETDATE()),0)
KM
2009-10-01 11:48:19