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