July 18, 2016 at 3:00 pm
my records are updated 12am I need to pull all records from morning 6am to 12am as of yesterday.
example: today is 7/18
so need all records from 7/17 6am till 7/17 12am
how to round time?
July 18, 2016 at 3:04 pm
komal145 (7/18/2016)
my records are updated 12am I need to pull all records from morning 6am to 12am as of yesterday.example: today is 7/18
so need all records from 7/17 6am till 7/17 12am
how to round time?
something like this:
SELECT ...
FROM ...
WHERE
DateBasedColumn >= dateadd(day, datediff(day,0,getdate()) - 1, 0) and
DateBasedColumn < dateadd(day, datediff(day,0,getdate()), 0)
July 18, 2016 at 4:35 pm
how can we get particular time like 6am ?
July 18, 2016 at 4:41 pm
The "standard/best-practice" way to get yesterday's date is:
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0)
Based on that, then make the necessary 6-hour adjustment on the starting time [the "+" in the hours is not required, it's just to make the code more obvious (hopefully)]:
WHERE
datetime_column >= DATEADD(HOUR, +6, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0)) AND
datetime_column < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 18, 2016 at 4:43 pm
Thank you
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply