SQL Server 2008 - How to convert GMT(UTC) datetime

问题:

I have an insert proc that passes in GETDATE() as one of the values because each insert also stores when it was inserted. This is hosted on SQL Azure - which uses GMT.

Now, when I am receiving messages, I have the GMT date stored for each of them in their timestamp columns, how do I convert this to the local datetime for wherever you are when you are accessing my page?

Thanks.

回答1:

You could do something like this:

declare @InputUtcDateTime datetime2 = '2011-05-20 06:30:18'

declare @LocalDateTime datetime2 = dateadd(minute, datepart(TZoffset, sysdatetimeoffset()), @InputUtcDateTime)
print @LocalDateTime

or

declare @InputUtcDateTime datetime2 = '2011-05-20 06:30:18'

declare @LocalDateTime datetime2 = dateadd(minute, datediff(minute, sysutcdatetime(), sysdatetime()), @InputUtcDateTime)
print @LocalDateTime

回答2:

Aside from the Daylight Savings issue, why not simplify with:

yourDateTime - getutcdate() + getdate()

回答3:

For MST as an example... considering each DTM is stored in GMT already, that simplifies things..

SWITCHOFFSET(CONVERT(DATETIMEOFFSET, [ColumnName]), '-07:00')

Now, if your local date/time is something other than GMT/UTC, you'll likely want to use the following...

SWITCHOFFSET(TODATETIMEOFFSET([ColumnName], datepart(tz,sysdatetimeoffset())),'+00:00')

Here's the breakdown.

• SWITCHOFFSET - converts a DateTimeOffset value to a different timezone, while preserving the offset.
• TODATETIMEOFFSET - converts a DateTime value to a DateTimeOffset value at a specified timezone.
• DATEPART - in this case is getting the timezone part of the local datetime.
• '+00:00' - the target offset, in the second example is UTC/GMT target, from local... the former example is to MST.

NOTE/WARNING: I don't believe that this accounts for Daylight Savings Time, which could be an issue for you. If absolute preservation isn't necessary, you may want to simply add a secondary column, with the rough conversion and go forward safely.

You may want to abstract the logic into a function call, in order to account for the preservation of DST... it shouldn't be excessively difficult to do, though.

回答4:

Here's a function which works on historic data. I wrote it for British Summer time - which unfortunately occurs on the last Sunday of the months of March and October, making the logic a little convoluted.

Basically the hard coded date part 01/03 is looking for the last Sunday in March and 01/10 is looking for the last Sunday in October (which is when the clocks go forward and back here). NOTE: IF YOUR SERVER IS USING NATIVE US DATES REVERSE THESE TWO DATE PARTS TO 03/01 and 10/01!!!!

So you feed it a UTC date and it'll automatically work out whether an historic date is BST or GMT. Not the best thing to use on a big data set but it's a solution.

Run this script to create the function and call it inline in your select. SQL 2008 has a problem with user defined functions, it seems, it puts a redline under the code, but it still runs it as long as you use the dbo prefix (SELECT dbo.UTCConvert(yourdate) to run it)

CREATE FUNCTION [dbo].[UTCConvert] 
(

    @p1 datetime
)
RETURNS datetime
AS
BEGIN

    DECLARE @Result datetime


RETURN CASE 
WHEN
@p1 >
(DATEADD(day,DATEDIFF(day,'19000107',DATEADD(month,DATEDIFF(MONTH,0,'01/03/' + CAST(DATEPART(year,@p1) as CHAR)),30))/7*7,'19000107'))
AND
@p1<
(DATEADD(day,DATEDIFF(day,'19000107',DATEADD(month,DATEDIFF(MONTH,0,'01/10/' + CAST(DATEPART(year,@p1) as CHAR)),30))/7*7,'19000107'))
THEN (DATEADD(HH, 1, @p1)) 
ELSE @p1
END
END