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

• Matt Sieker: This is the one I ended up going with. All the more so once I discovered that DateTimeOffsetFromParts is new to SQL2012.

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.