UTC datetime offset

Question

I need to get timestamps from Axapta-tables in TSQL, without timezone and / or daylight-bias-offsets for each time, eg from table JMGABSENCECALENDAR.

Taking this as initial approach, and regaring this, it works for current time. But reading data from the table referring to other timestamps, the solution provided in the second link doesn't get the information about daylight to the specified time.

For example:

I add an absence for today ( 2012-01-07 ). Now, using SSMS, reading this dataset leads to

starttime = 2013-01-06 23:00:00.000 and endtime = 2013-01-07 23:00:00.000

That's ok, and I can use

DECLARE @UTCOffset SMALLINT

EXEC master..xp_regread
    'HKEY_LOCAL_MACHINE',
    'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
    'ActiveTimeBias',
    @UTCOffset OUTPUT

SELECT DATEADD(MINUTE, @UTCOffset, GETDATE()) AS UTCTime

to remove offset. This works fine on actual dates, but what's the right way to remove offset for past or future times, eg 2012-07-01 ?

Here, the offset is 120 minutes, because of summertime. Reading Reg-Value only returns current offset.

The task has to be solved in TSQL 2008.

score 2 · Accepted Answer · answered Jan 22 '13 at 17:40

I had a same problem, but it was in a complete different setting. I had nothing to do with axapta. However, i had the problem that i had to know the UTC offset of different times. The tricky part here is the fact that different countries use a different approach towards daylight saving times, and therefor a difference in the offset may occur for different countries at the same time.

What i did was to create a lookup table where i put in the dates that UTC offsets change, these are known dates. I gave it an offset column so i could easily look up the offset that i needed for a certain date, using the between operator.

It worked for me, maybe this solution can provide you something?

Ps. You don't have to lookup the UTC date offset from out of the registry. Using the function getutcdate() will give you the same ;) Using that inside a DATADD makes it a little more readable ;)

Have fun and i hope i could contribute to your problem...

Thanks for your answer - but a lookup table wasn't my first choice. The change-dates of UTC may vary from year to year and I don't want to have anothe table which needs yearly support ;) +1 for the info that getutcdate() may replace the regread! — Nico, Jan 23 '13 at 00:21
Nico, what i meant with such a table is that you can generate it once and use it for many years. In my case i could create a table that would give me all utc periods of the comming 15 years, which was more then sufficient for this customer. — Mark Kremers, Jan 24 '13 at 12:30

score 0 · Answer 2 · answered Jan 23 '13 at 18:12

Just because the daylight savings switch dates change from year to year and state to state, your only viable option is a lookup table.

You can find the data for example here http://www.timeanddate.com/time/dst/2013a.html

However, you might not have to maintain that list yourself. timeanddate.com has a calculator on their site. Others offer similar services. You could look for a public API and then use a few lines of CLR code to call that API from your database.

Or you could use such a service to maintain your own copy of that data. Having your own lookup table will be by far the fastest solution.

UTC datetime offset

2 Answers2

Linked