Sql applying a floor to Datetime, Where the floor is not standard e.g. every 2 hours, every 2 day etc

Question

Aim: Sql applying a floor to Datetime, Where the floor is not standard e.g. every 2 hours, every 3 day. Instead of the standard every hour, every day etc.

General purpose: To enable comparison of data for each collection period. when a collection period it larger then a standard unit of time. While keeping the benefits of Datetime type.

Example of the conversion:

floor a datetime to 2 hours

+==========================+=======================+
|        WhenCreated       |       Time_Slot       |
+==========================+=======================+
|   2019-02-28 00:34:00    | 2019-02-28 00:00:00   |
+--------------------------+-----------------------+
|   2019-02-28 01:34:00    | 2019-02-28 00:00:00   |
+--------------------------+-----------------------+
|   2019-02-28 02:00:00    | 2019-02-28 02:00:00   |
+--------------------------+-----------------------+
|   2019-02-28 02:20:00    | 2019-02-28 02:00:00   |
+--------------------------+-----------------------+
|   2019-02-28 03:59:00    | 2019-02-28 02:00:00   |
+--------------------------+-----------------------+

Current Solution:

inside the select statement will create an new column [Time_Slot] based on [WhenCreated]:

left(convert(varchar(30), WhenCreated, 102),4) + '-' + left(right(convert(varchar(30), WhenCreated, 102),5),2) + '-' + right(convert(varchar(30), WhenCreated, 102),2) + ' ' +
    case
        when left(cast(cast(WhenCreated as time) as varchar(30)),2) in ('00','01') then '00:00:00.000'
        when left(cast(cast(WhenCreated as time) as varchar(30)),2) in ('02','03') then '02:00:00.000'
        when left(cast(cast(WhenCreated as time) as varchar(30)),2) in ('04','05') then '04:00:00.000'
        when left(cast(cast(WhenCreated as time) as varchar(30)),2) in ('06','07') then '06:00:00.000'
        when left(cast(cast(WhenCreated as time) as varchar(30)),2) in ('08','09') then '08:00:00.000'
        when left(cast(cast(WhenCreated as time) as varchar(30)),2) in ('10','11') then '10:00:00.000'
        when left(cast(cast(WhenCreated as time) as varchar(30)),2) in ('12','13') then '12:00:00.000'
        when left(cast(cast(WhenCreated as time) as varchar(30)),2) in ('14','15') then '14:00:00.000'
        when left(cast(cast(WhenCreated as time) as varchar(30)),2) in ('16','17') then '16:00:00.000'
        when left(cast(cast(WhenCreated as time) as varchar(30)),2) in ('18','19') then '18:00:00.000'
        when left(cast(cast(WhenCreated as time) as varchar(30)),2) in ('20','21') then '20:00:00.000'
        when left(cast(cast(WhenCreated as time) as varchar(30)),2) in ('22','23') then '22:00:00.000'
    end as [Time_Slot]

Current Challenge: The current solution works, but it is messy. There has to be a simpler method. So what is a better way to do this?

Edit: fixed error in table.

Answer 1

Deconstruct the date into its parts, then reconstruct it using the necessary parts, adjusting the part that needs to be floor'd.

To floor an integer value to a multiplier, simply divide by the multiplier and multiply it again. This relies on integer division truncating the division result, e.g. 5 / 2 = 2, so 5 / 2 * 2 = 4, i.e. truncated to multiple of 2.

So, to floor the WhenCreated value to multiple of 2 hours:

DATETIMEFROMPARTS ( YEAR(WhenCreated), MONTH(WhenCreated), DAY(WhenCreated),
                    DATEPART(HOUR, WhenCreated) / 2 * 2, /*min*/0, /*sec*/0, /*milli*/0 )