如何将UCT中保存的日期转换为时区? [英] How to convert a date saved in UCT to Time Zone?
问题描述
我的服务器在UCT中节省时间,不包括dateTime2中的时区信息。服务器在-7 GMT。我需要创建一个显示记录但在TimeZone上为用户转换的SQL(+ 1 GMT)。
让我们想象一下我有这个记录'2014-05-27 01 :00:00'应显示为'2014-05-27 09:00:00'
使用以下脚本我无法获得所需的结果。你能指出我的问题吗?
SELECT CONVERT ( datetime ,
SWITCHOFFSET( CONVERT (datetimeoffset,
' 2014-05-27 01:00:00'),
DATENAME(TzOffset,SYSDATETIMEOFFSET())))
以下代码可以帮助您:
SELECT GETUTCDATE()
SELECT DATEADD(HH,5,GETUTCDATE())
HH是小时部分。这可能是时区差异。获取UTC日期将为您提供utc日期
由此您可以获得当前系统时区
------------- ------------------------------
DECLARE @ TimeZone VARCHAR ( 50 )
EXEC MASTER.dbo.xp_regread ' HKEY_LOCAL_MACHINE',
' SYSTEM \ CurrentControlSet \ Control\TimeZoneInformation',
' TimeZoneKeyName', @ TimeZone OUT
SELECT @ TimeZone
现在Utc到当前时区
----------- --------------
声明 @ offset 十进制
设置 @ offset =(选择 DATEDIFF(分钟, GETUTCDATE(),GETDATE()))
选择 dateadd(分钟, @ offset ,GETUTCDATE ())
My server save time in UCT not including Time Zones info in dateTime2. Server is in -7 GMT. I need to create an SQL which show records but converted at TimeZone for User (+1 GMT).
Let's imagine I have this record '2014-05-27 01:00:00' Should be displayed as '2014-05-27 09:00:00'
Using the following script I am not able to get the result desired. Could you please point me out the problem?
SELECT CONVERT(datetime,
SWITCHOFFSET(CONVERT(datetimeoffset,
'2014-05-27 01:00:00'),
DATENAME(TzOffset, SYSDATETIMEOFFSET())))
Here is the code that might help you:
SELECT GETUTCDATE() SELECT DATEADD(HH,5,GETUTCDATE())
HH is the hours part. This can be the time zone difference. Get UTC date will give you the utc date
By this u can get current system time zone
-------------------------------------------
DECLARE @TimeZone VARCHAR(50) EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation', 'TimeZoneKeyName',@TimeZone OUT SELECT @TimeZone
Now Utc to Current time Zone
-------------------------
declare @offset decimal set @offset= (select DATEDIFF(minute,GETUTCDATE(), GETDATE() )) select dateadd(minute, @offset,GETUTCDATE())
这篇关于如何将UCT中保存的日期转换为时区?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!