July 25, 2016 at 11:59 am
I need to pull previous day data from midnight to midnight . Meaning 7/25 12:00 am to 7/25 11:59pm.
how to get this?
July 25, 2016 at 12:06 pm
komal145 (7/25/2016)
I need to pull previous day data from midnight to midnight . Meaning 7/25 12:00 am to 7/25 11:59pm.how to get this?
...WHERE DateCol >= '20160725' and DateCol < '20160726'
For a dynamic 'Previous Day', use
DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) - 1, 0)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 25, 2016 at 12:16 pm
Alternatively, you could cast your DATETIME data as DATE and compare those.
WHERE CAST(DateCol AS DATE) = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)
Since CASTing from DATETIME to DATE is still SARGable, this should perform fairly well.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 25, 2016 at 12:38 pm
komal145 (7/25/2016)
I need to pull previous day data from midnight to midnight . Meaning 7/25 12:00 am to 7/25 11:59pm.how to get this?
Quick question, what data type is your date column?
😎
July 25, 2016 at 12:50 pm
drew.allen (7/25/2016)
Alternatively, you could cast your DATETIME data as DATE and compare those.
WHERE CAST(DateCol AS DATE) = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)
Since CASTing from DATETIME to DATE is still SARGable, this should perform fairly well.
Drew
Reminder: don't use this on other data types as the queries might become non-SARGable.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply