Search code examples
sql-serverdatetime2

datetime2 truncate to the last full 30 minute period

I'm trying to create a function that's rounding off to the last completed 30 minute period. My definition of a 30 minute period is every hour and half hour (12, 12:30, 13, 13:30, etc.)

So if the time is 22:16 I would like it to truncate to 22:00 however, if the time is 22:48 I would like the result to be 22:30.

I have sofar tested:

DECLARE @RUN_DATE datetime2(1) = GETDATE();
DECLARE @Rounded datetime2(1) = dateadd(hour, datediff(hour, 0, dateadd(mi, 0, @RUN_DATE)), 0)

However, this rounds 22:38 to 23:00

Any tips are highly appreciated :)

Solution

  • You can use the following trick

    DECLARE @RUN_DATE datetime2(1) = GETDATE();
DECLARE @Rounded datetime2(1) = DATEADD(mi, DATEDIFF(mi, 0, @RUN_DATE)/30*30, 0)
select @rounded