0

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 :)

Petter Östergren
  • 973
  • 4
  • 14
  • 27

1 Answers1

1

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
Stu
  • 30,392
  • 6
  • 14
  • 33
  • 1
    This is brilliant. For my own sanity I just validated it with `DECLARE @RUN_DATE datetime2(1) = '2021-04-29 23:10:00.0';`and it returned `23:00`as expected. Thank you! – Petter Östergren Apr 29 '21 at 20:50