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.
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.