Calculating a time in a time zone from multiple dates in SQL

Question

I'm trying to calculate what time a certain time in a time zone is today, so I can schedule something to happen at that time in that time zone.

I've got a table with what I have termed the Nominal Time, which is stored as a datetimeoffset with an arbitrary date, as the only parts I care about is the time and the time zone offset. So, the Nominal Time column has values along the lines of:

2014-07-01 10:00:00.0000000 +02:00
2014-07-01 10:00:00.0000000 -05:00
2014-07-01 10:00:00.0000000 -07:00
2014-07-01 10:00:00.0000000 +01:00

(In this case 10am is my time I want to schedule this events). From this, I want to get that time today, so these would become:

2014-07-02 10:00:00.0000000 +02:00
2014-07-02 10:00:00.0000000 -05:00
2014-07-02 10:00:00.0000000 -07:00
2014-07-02 10:00:00.0000000 +01:00

when run at the date of this writing (2014-07-02). I currently have SQL that does this, but I don't really like it:

With    NominalTimes as (select Id, NominalTime, SYSDATETIMEOFFSET() Now 
                        from FaxQueue where status=0),
        CalcTimes as (select Id, NominalTime, Now, DATEPART(year,Now) NomYear,
                        DATEPART(month,Now) NomMonth,DATEPART(day,Now) NomDay,
                        DATEPART(hour,NominalTime) NomHour,DATEPART(minute,NominalTime) NomMinute,
                        DATEPART(tzoffset,NominalTime) NomOffset from NominalTimes)
select Id, NominalTime, Now, 
    DATETIMEOFFSETFROMPARTS(Nomyear,NomMonth,NomDay,NomHour,NomMinute,0,0,NomOffset/60,NomOffset%60,0) 
    from CalcTimes

(Excuse the excessive CTEs; I'm trying to build this up bit by bit.) The end goal of this is to have a query that returns a list of rows where the nominal time happens within the next, say, hour (actual window size isn't important).

I will also note that from the function of the program, I do not need to worry about a time straddling a daylight saving time transition (the program is meant to run before DST happens in a time zone, and deliver a notification).

Is there a better way of doing these date calculations in SQL, or is this really about as good as it's going to get?

Answer 1

First, let me restate your problem, to make sure I understand it correctly. You want to take the NominalTime column, which is of type datetimeoffset, and replace the date part with today's date, where "today" is defined according to the timezone in which the SQL Server is running. The time and timezone offset will remain unchanged, even across DST boundaries.

To roll one field of a datetime-like object forward or backward, use the DATEADD() function:

SELECT Id
     , NominalTime
     , SYSDATETIMEOFFSET() AS Now
     , DATEADD(day, DATEDIFF(day, NominalTime, SYSDATETIMEOFFSET()), NominalTime)
    FROM FaxQueue
    WHERE status = 0;

SQL Fiddle demonstration

Answer 2

Formatting

First off, I reformatted your code to make it easier to read. When writing SQL it is best to be consistent with capitalization, and using indenting makes it much easier to read. So here is your code in my (personally preferred) format, including SQL keywords in all caps and indentation.

WITH NominalTimes AS     
    (    
    SELECT     
        Id,     
        NominalTime,     
        SYSDATETIMEOFFSET() Now     
    FROM FaxQueue     
    WHERE  status=0    
    ),    
CalcTimes AS     
    (    
    SELECT 
        Id,     
        NominalTime,     
        Now,     
        DATEPART(year,Now) NomYear,    
        DATEPART(month,Now) NomMonth,    
        DATEPART(day,Now) NomDay,    
        DATEPART(hour,NominalTime) NomHour,    
        DATEPART(minute,NominalTime) NomMinute,    
        DATEPART(tzoffset,NominalTime) NomOffset     
    FROM NominalTimes    
    )    
SELECT 
    Id,     
    NominalTime,     
    Now,     
        DATETIMEOFFSETFROMPARTS    
        (    
        Nomyear,    
        NomMonth,    
        NomDay,    
        NomHour,    
        NomMinute,    
        0,    
        0,    
        NomOffset/60,    
        NomOffset%60,    
        0    
        )     
FROM CalcTimes

Method

That said, my opinion is that the method you are using, while functional, is overkill. Remember KISS (Keep It Simple Stupid). Here is how I would write this to achieve the same result much more simply.

This code works

SELECT    
  CONCAT    
  (    
  CAST(SYSDATETIMEOFFSET AS VARCHAR),    
  ' ',    
  DATEPART(hour,NominalTime),    
  ' ',    
  DATEPART(minute,NominalTime),    
  ' ',    
  CAST(DATEPART(tzoffset,NominalTime) AS VARCHAR)    
  ) as TodayOffset    
FROM FaxQueue    
WHERE  status=0    
;

Example output

TODAYOFFSET     
Jul 2 2014 6:51PM 10 0 120     
Jul 2 2014 6:51PM 10 0 -300     
Jul 2 2014 6:51PM 10 0 -420     
Jul 2 2014 6:51PM 10 0 60 

It looks slightly different from the input data but it says the same thing. It converted the tzoffset to minutes.

Another approach, perhaps even better:

DECLARE @DateDiff INT    
SET @DateDiff =     
  (    
  SELECT DATEDIFF(DAY, '2014-07-01', CURRENT_TIMESTAMP)    
  ) -- or whatever your generic date is    
SELECT DATEADD(DAY, @DateDiff, NominalTime) AS TodayOffset    
FROM FaxQueue    
WHERE Status=0

Output:

TODAYOFFSET     
2014-07-02 10:00:00.0000000 +02:00     
2014-07-02 10:00:00.0000000 -05:00    
2014-07-02 10:00:00.0000000 -07:00     
2014-07-02 10:00:00.0000000 +01:00 

Hope this help!

! EDIT !

I had a type conversion error in the original query due to concatenating DATE and VARCHAR into the same column. The above code is revised and functional.