I have a DateTime dimension with dates like this:
Datetime Date hour
--------------------------------------
2026-01-01 00:00:00.0000000 24
2025-12-31 23:00:00.0000000 23
2025-12-31 22:00:00.0000000 22
2025-12-31 21:00:00.0000000 21
2025-12-31 20:00:00.0000000 20
2025-12-31 19:00:00.0000000 19
2025-12-31 18:00:00.0000000 18
2025-12-31 17:00:00.0000000 17
2025-12-31 16:00:00.0000000 16
2025-12-31 15:00:00.0000000 15
2025-12-31 14:00:00.0000000 14
2025-12-31 13:00:00.0000000 13
2025-12-31 12:00:00.0000000 12
2025-12-31 11:00:00.0000000 11
2025-12-31 10:00:00.0000000 10
2025-12-31 09:00:00.0000000 9
2025-12-31 08:00:00.0000000 8
2025-12-31 07:00:00.0000000 7
2025-12-31 06:00:00.0000000 6
2025-12-31 05:00:00.0000000 5
2025-12-31 04:00:00.0000000 4
2025-12-31 03:00:00.0000000 3
2025-12-31 02:00:00.0000000 2
2025-12-31 01:00:00.0000000 1
Due to systemtime i need that my hours start at 6 o'clock instead, but how do i do that? I could create a temptable with hardcoded values and join on, but theres gotta be a simpler way?
Desired result:
Datetime Date hour
--------------------------------------
2026-01-01 00:00:00.0000000 19
2025-12-31 23:00:00.0000000 18
2025-12-31 22:00:00.0000000 17
2025-12-31 21:00:00.0000000 16
2025-12-31 20:00:00.0000000 15
2025-12-31 19:00:00.0000000 14
2025-12-31 18:00:00.0000000 13
2025-12-31 17:00:00.0000000 12
2025-12-31 16:00:00.0000000 11
2025-12-31 15:00:00.0000000 10
2025-12-31 14:00:00.0000000 9
2025-12-31 13:00:00.0000000 8
2025-12-31 12:00:00.0000000 7
2025-12-31 11:00:00.0000000 6
2025-12-31 10:00:00.0000000 5
2025-12-31 09:00:00.0000000 4
2025-12-31 08:00:00.0000000 3
2025-12-31 07:00:00.0000000 2
2025-12-31 06:00:00.0000000 1
2025-12-31 05:00:00.0000000 24
2025-12-31 04:00:00.0000000 23
2025-12-31 03:00:00.0000000 22
2025-12-31 02:00:00.0000000 21
2025-12-31 01:00:00.0000000 20
And for any questions about why I dont hold a DateDimension and a TimeDimension. This cannot forefill my requirements since I have many more business rules on my datetime dimensions
At a guess...
UPDATE YourTable
SET [Date hour] = IIF(DATEPART(HOUR,DATEADD(HOUR, -5, [datetime])) = 0,24,DATEPART(HOUR,DATEADD(HOUR, -5, [datetime]));