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