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

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?

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

• This is the one I ended up going with. All the more so once I discovered that DateTimeOffsetFromParts is new to SQL2012. Jul 7, 2014 at 17:30

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

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.