Rounding DateTime in SQL

user_7630528 0
Viewed 167

I have DateTime data in a MS SQL database with the following format:

2020-05-07 22:35:00

I am trying to create a query that only captures data from the last 24 hours of operations. However, our operations KPIs are measured from 6AM-6AM. I would like to round the date based on time. Anything before 6AM will be counted as the day before.

2020-05-07 05:45:00 -> 2020-05-06 (Before 6AM)
2020-05-07 06:30:00 -> 2020-05-07 (After 6AM)

So far I have been successful in pulling the previous days activity, but am struggling to shift the timeframe to round down anything before 6AM

SELECT 
      end_date 
FROM data sint
WHERE sint.end_date >= dateadd(day,datediff(day,1,GETDATE()),0)
AND sint.end_date < dateadd(day,datediff(day,0,GETDATE()),0)
datetime sql-server sql
3 Answers

You can add six hours to the current date (without the time) for the comparison: 

where int.end_date < dateadd(hour, 6, convert(datetime, convert(date, getdate()))) and
      int.end_date >= dateadd(hour, 6 - 24, convert(datetime, convert(date, getdate())))

Note that the conversion to date removes the time component.

user_11440350

first get the time part. Compare the time part with 6AM and run your expression.

In below code the time part is compared with the date '1900-01-01 06:00:00' which is 6AM in default date format.

select 
  case when cast(date as time(0)) < '1900-01-01 06:00:00' 
       then cast(date - 1 as date) 
            else cast(date as date) 
  end as newdate
  from temp_date;
user_30824220

I would do the same as Gordon only the opposite side.

I would subtract 6 hours from end_date and then compare.

where convert(date, dateadd(hour, -6, int.end_date)) = convert(date, getdate())

Though this method might not work well if you're actually using getdate(). It will return nothing if it is ran between midnight and 6 am (unless you have future dates).

user_120554790
Related