如何从Sql Server中的TimeZoneOffset了解TimeZone StandardName或DayLightName [英] How to know the TimeZone StandardName or DayLightName from TimeZoneOffset in Sql Server

问题描述

我使用的是Sql Sever 2008 R2。有没有办法从timezoneoffSet中识别时区标准名称或日光名称。



例如我有2013-09-26 03：00：00.0000000 -04 ：00并且从上面需要东部夏令时间。



如何在SQL服务器中完成此操作？



任何建议都将受到赞赏。

I am using Sql Sever 2008 R2. Is there a way to identify the time zone Standard name or daylight name from timezoneoffSet.

For example I have "2013-09-26 03:00:00.0000000 -04:00" and need "Eastern Daylight Time" from above.

How can I accomplish this in SQL server ?

Any suggestions will be appreciated.

推荐答案

无需特殊功能：

No special function required:

DECLARE @dtOne [datetime]
SET @dtOne = CAST('2013-09-26 03:00:00.000' AS [datetime])
DECLARE @dtTwo [datetime2]
SET @dtTwo = CAST('2013-09-26 03:00:00.0000000' AS [datetime2])
DECLARE @dtThree [datetime2]
SET @dtThree = CAST('2013-09-26 03:00:00.0000000 -04:00' AS [datetimeoffset])
SELECT DATEPART("Hh",@dtOne)
SELECT DATEPART("Hh",@dtTwo)
SELECT DATEPART("Hh",@dtThree)

/ * MS某处* /

/* MS somewhere */

SELECT DATEADD(minute, DATEDIFF(minute,getutcdate(),getdate()), @dtThree) AS [crossing]

获取：

Gets:

[crossing]
~~~~~~~~~~
2013-09-25 20:00:00.0000000

来自BOL的例子：

Example from BOL:

SELECT 
     CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time' 
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date' 
    ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS 
        'smalldatetime' 
    ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime' 
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS 
        'datetime2'
    ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 
        'datetimeoffset';

产生：

Produces:

time	date	smalldatetime	datetime	datetime2	datetimeoffset
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
12:35:29.1234567	2007-05-08	2007-05-08 12:35:00	2007-05-08 12:35:29.123	2007-05-08 12:35:29.1234567	2007-05-08 12:35:29.1234567 +12:15

具体来说，EDT是派生出来的......但是使用DATEDIFF ...看看偏移确实在前一天得到了。所以有一些[日期时间] 24小时难度的交叉，它是由TSQL完成的。无需计算。

Specifically, EDT is derived ... but using DATEDIFF ... see that the offset does, in fact, get the day before. So there's some crossing of the [datetime] 24-hour difficulty and it's done by TSQL. No computations necessary.

这篇关于如何从Sql Server中的TimeZoneOffset了解TimeZone StandardName或DayLightName的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！