Monday, March 12, 2012

Getting Previous Date SQL Script

Hello,

I have a table that also has a timestamp field I want to get any data that was inserted into that table on the previous day even if the month changes and i have no clue how to go about that

Tdar

Is your field actually a "timestamp" or a "datetime" field?|||

If your "timestamp" is actually a "datetime" field you can do:

select *
from yourTable
where dateTimeColumn < cast ( floor(cast(getdate() as float)) as datetime)
and dateTimeColumn >= cast ( floor(cast(getdate() as float)) - 1 as datetime)

or also

select *
from yourTable
where dateTimeColumn < convert(datetime, convert (varchar(10), getdate(), 101))
and dateTimeColumn >= convert(datetime, convert (varchar(10), getdate(), 101)) - 1

|||

SELECT * FROM yourDatesTable WHERE DATEDIFF(day, yourDateColumn, getdate())=1

|||Waldrop's is the better option here, because it doesn't need to apply a function to yourDateColumn before doing the comparison. This means that it can use indexes effectively.

Rob|||

limno wrote:

SELECT * FROM yourDatesTable WHERE DATEDIFF(day, yourDateColumn, getdate())=1


Another approach:
SELECT * FROM yourDatesTable WHERE yourDateColumn>DATEADD(day,-2,getdate()) AND yourDateColumn<=DATEADD(day,-1,getdate())

No comments:

Post a Comment