UTC日期时间偏移 [英] UTC datetime offset

问题描述

我需要从TSQL中的Axapta表获取时间戳，而每次都没有时区和/或daylight-bias-offsets，例如从表JMGABSENCECALENDAR中获取.

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.

例如:

我今天要缺席(2012-01-07). 现在，使用SSMS，读取此数据集将导致

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

开始时间= 2013-01-06 23:00:00.000 和结束时间= 2013-01-07 23:00:00.000

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

可以，我可以使用

DECLARE @UTCOffset SMALLINT

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

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

以删除偏移量.这在实际日期上可以正常工作，但是删除过去或将来时间的偏移量的正确方法是什么，例如2012-07-01?

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 ?

在这里，由于夏季，偏移量是120分钟.读取Reg-Value仅返回当前偏移量.

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

该任务必须在TSQL 2008中解决.

The task has to be solved in TSQL 2008.

推荐答案

我遇到了同样的问题，但是设置完全不同.我和axapta无关. 但是，我有一个问题，我必须知道不同时间的UTC偏移量.这里最棘手的部分是，不同国家/地区采用不同的夏令时方法，因此，不同国家/地区可能同时出现偏移量的差异.

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.

我所做的是创建一个查找表，在其中放置U​​TC偏移量更改的日期，这些日期是已知的日期.我给它提供了一个偏移量列，这样我就可以使用between运算符轻松查找某个日期所需的偏移量.

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.您不必从注册表外查找UTC日期偏移量.使用功能getutcdate()将为您提供相同的;)在DATADD中使用该功能使其更具可读性;)

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

这篇关于UTC日期时间偏移的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！