Upper and lower limit based on hour

Question

I have a first date field based on utc-6 named date_utc_minus_6. I managed to create a new version of this field based on utc+1 named date_utc_1. Now what I am trying to do is to create two fields that truncate my date (the one based on utc+1) to the hour (upper and lower limit).

For example:

RECORD 1 :

date_utc_minus_6 = 02/07/2024 8:15 AM
date_utc_1 = 02/07/2024 2:15 PM
field_lower_limit = 02/07/2024 2:00 PM
field_upper_limit = 02/07/2024 3:00 PM

Here Is what I tried :

getdate() as date_utc_minus_6, /*system date field*/
date_utc_minus_6 AT TIME ZONE 'Central Standard Time' AT TIME ZONE 'Central European Standard Time' AS date_utc_1,
DATEADD(hour, DATEDIFF(hour, 1, date_utc_1), 0) as field_lower_limit,
DATEADD(hour, 1, field_lower_limit)  as field_upper_limit

I do not understand why I get:

field_lower_limit = 02/06/2024 2:00 PM
field_upper_limit = 02/06/2024 3:00 PM
on 6th feb instead of 7th.

Does someone know where I should correct my code?

For the moment, it works with:

dateadd(day,1,DATEADD(hour, DATEDIFF(hour, 1, date_utc_1 ), 0)) as field_lower_limit 

dateadd(day,1,DATEADD(hour, 1, field_lower_limit )) as field_upper_limit

BUt I'm not very confident about my code.

Answer 1

If you look at what DATEDIFF(hour, 1, @date_utc_1) returns you'll see what is probably the problem. You're asking for the number of hours between 1 and today, which will be some big number.

Try this instead,

declare @date_utc_minus_6   DATETIME2 = getdate() AT TIME ZONE 'Central Standard Time'
declare @date_utc_1          DATETIME2 = @date_utc_minus_6 AT TIME ZONE 'Central European Standard Time' 
declare @field_lower_limit   DATETIME2 = DATEADD(hour, DATEDIFF(hour, 1, @date_utc_1), 0) 
declare @field_upper_limit  DATETIME2 = DATEADD(hour, 1, @field_lower_limit)  

SELECT 'date_utc_minus_6' as na, @date_utc_minus_6 UNION ALL 
SELECT 'date_utc_1' as na, @date_utc_1 UNION ALL 
SELECT 'field_lower_limit' as na, @field_lower_limit UNION ALL 
SELECT 'field_upper_limit' as na, @field_upper_limit

SELECT field_lower_limit = DATEADD (hour , DATEPART(hour, @date_utc_1) , CAST(CAST(@date_utc_1 as date) as datetime))
    , field_upper_limit = DATEADD (hour , DATEPART(hour, @date_utc_1) +1, CAST(CAST(@date_utc_1 as date) as datetime))

This is using what I had available in SQL016 so there may be much better ways to do this later versions....

All we do here is get the date part only of the utc_1 date and then add the number of hours from Utc_1 and hours+1 for the upper value.

Here's a fiddle showing this in action, the first set of results are your current results. the last select is the modified version.

https://dbfiddle.uk/6yGn6iX4