SQL DateTimes stored in UTC get beginning of day based on offset

user_55747 0
Viewed 1533

This may be the most pathetic question ever asked related to SQL and date/time values, but I could use some help...

Trying to setup a function/job that will run at a specified time or times in eastern, mountain, central, and pacific time zones (in theory other zones would work too). The system will identify which users belong to each timezone and then output data from the system highlighting what they've accomplished for the current day.

Here is my challenge, I know all date/time values are stored on the SQL DB in UTC. I can apply the offset and convert those times to local time zones. Rather than convert tens of thousands of date/time values to local time and make comparisons there, it'd be cleaner (I think) to simply adjust the beginning and ending date/time values of UTC within the stored procedure.

On the west coast it is currently just about 2017-09-30 14:30:00 and in UTC is 2017-09-30 21:30:00, this clearly demonstrates a 7 hour time zone difference right now which means "today" from a user perspective technically started at 2017-09-30 07:00:00 and will end on 2017-10-01 06:59:999 in UTC.

What is the best way of establishing these date/time values for a users beginning of day and ending of day values?

UPDATES

I currently have this code...

DECLARE @InputDate as DateTime
DECLARE @InputEndDate as DateTime
DECLARE @InputDateWithOffset as DateTimeOffSet
DECLARE @InputEndDateWithOffset as DateTimeOffSet

SET @InputDate = '2017-09-28'
SET @InputDateWithOffset = @InputDate AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time'

SET @InputEndDate = DATEADD(day, 1, DATEADD(ms, -3, @InputDate))
SET @InputEndDateWithOffset = @InputEndDate AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time'

SELECT 
    @InputDate AS InputDate, @InputEndDate AS InputEndDate, 
    @InputDateWithOffset AS InputDateWithOffset, 
    @InputEndDateWithOffset AS InputEndDateWithOffset

Which outputs the following: enter image description here

The last two columns appear to be correct as it would represent both the beginning of the Input Date and the ending of the Input Date as the Input Date is going to be the local date of the execution...

When I take the @InputDateWithOffset and @InputEndDateWithOffset against my table values with datetimes in UTC, it appears the only dates being returned are those that fall on 2017-09-28 and seems to disregard the comparisons to the Offset date/times.

localtime utc datetime sql-server sql
1 Answers

Your update is mostly correct. However, you're missing a "start of day" operation, which needs to be done in local time.

Consider:

DECLARE @InputStartUTC as DATETIME, @InputEndUTC as DATETIME
DECLARE @InputStartDTO as DATETIMEOFFSET, @InputEndDTO as DATETIMEOFFSET
DECLARE @InputStartDTOatStartOfDay as DATETIMEOFFSET, 
        @InputEndDTOatStartOfDay as DATETIMEOFFSET

DECLARE @tz as VARCHAR(50) = 'Pacific Standard Time'

SET @InputStartUTC = '2017-09-28 00:00:00'
SET @InputStartDTO = @InputStartUTC AT TIME ZONE 'UTC' AT TIME ZONE @tz
SET @InputStartDTOatStartOfDay = CAST(CAST(@InputStartDTO as DATE) as DATETIME)
                                 AT TIME ZONE @tz

SET @InputEndUTC = DATEADD(day, 1, @InputStartUTC)
SET @InputEndDTO = @InputEndUTC AT TIME ZONE 'UTC' AT TIME ZONE @tz
SET @InputEndDTOatStartOfDay = CAST(CAST(@InputEndDTO as DATE) as DATETIME)
                               AT TIME ZONE @tz

SELECT 
    @InputStartUTC as InputStartUTC, @InputEndUTC as InputEndUTC, 
    @InputStartDTO as InputStartDTO, @InputEndDTO as InputEndDTO,
    @InputStartDTOatStartOfDay as InputStartDTOatStartOfDay,
    @InputEndDTOatStartOfDay as InputEndDTOatStartOfDay

Also, notice I did not subtract three milliseconds from your end date. Rather than trying to figure out .997 or .999 or whatever, the better approach is to query using a half-open interval. In other words, start <= value AND end > value.

user_6348240
Related