SQL Server 2008 - How to convert GMT(UTC) datetime to local 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 col开发者_运维问答umns, how do I convert this to the local datetime for wherever you are when you are accessing my page?

Thanks.

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

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

yourDateTime - getutcdate() + getdate()

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.

CREATE FUNCTION [dbo].[fn_DateTime_GMTFromLocal](@LocalTime DATETIME)
RETURNS DATETIME2(3)
AS
BEGIN
/*
=============================================
 Author:        Mark Griffiths
 Create date:   29/05/2018
 Description:   BST runs from 02:00AM on the last Sunday of March to the same time on the last Sunday of October.       
    The Series of DATEDIFFs and DATEADDS below function as follows
    1   ●   Count the number of months there have been between the given date and start of computer time
    2   ●   Add that number of months to the end of the first month to get the end of the given month
    3   ●   Count the number of days there have been between the end of the given month and the first Saturday
    4   ●   Add that number of days to the calculated end of the given month
    5   ●   Add Two hours to that time as the clocks go back at 02:00 in the morning

    I know that the tabbing below makes it all look odd, but the description above is the best way I could find to comment things, given the nesting...
    The comments in the code below should help find the nesting levels and the numbers refer to the bullet points above.
=============================================
-- Test Variables --
DECLARE @GMTime DATETIME2(3) = '2018-05-01 12:00:00.000'
*/

    DECLARE @RealTime As DATETIME2(3)
    DECLARE @Year VARCHAR(4)
    SET @Year = CONVERT(VARCHAR,DATEPART(YEAR,@GMTime))
    DECLARE @StartOfBST AS DATETIME
    DECLARE @EndOfBST AS DATETIME
    SELECT
        @StartOfBST =
        DATEADD     -----------------------------------------------------------------------------------------
            (                                                       --                                      |
             HOUR                                                   --                                      |
            ,2                                                      --                                      |
            ,DATEADD    -----------------------------------------------------------------------------       |
                (                                                   --                              |       |
                 DAY                                                --                              |       |
                ,DATEDIFF       -------------------------------------------------------------       |       |
                    (                                               --                      |       |       |
                     DAY                                            --                      |       |       |
                    ,'19000107'                                     --                      |       |       5
                    ,DATEADD            ---------------------------------------------       |       |       |
                        (                                           --              |       3       4       |
                         MONTH                                      --              |       |       |       |
                        ,DATEDIFF(MONTH,0,CONVERT(DATE,'03/01/' + @Year)) -- 1      2       |       |       |
                        ,CONVERT(DATE,'01/31/1900')                 --              |       |       |       |
                        )               ---------------------------------------------       |       |       |
                    )/7*7       -------------------------------------------------------------       |       |
                    ,'19000107'                                     --                              |       |
                )               ---------------------------------------------------------------------       |
            ),      -----------------------------------------------------------------------------------------
    @EndOfBST =
        DATEADD(HOUR,2,DATEADD(day,DATEDIFF(day,'19000107',DATEADD(month,DATEDIFF(MONTH,0,CONVERT(DATE,'10/01/' + @Year)),30))/7*7,'19000107'))
    SET @RealTime = CASE
                    WHEN @GMTime BETWEEN @StartOfBST AND @EndOfBST THEN DATEADD(HOUR,-1,@GMTime)
                    ELSE @GMTime
                END
RETURN @RealTime;
--SELECT @RealTime
END

*EDIT: Changed CONVERT(DATE,'01/30/1900') to CONVERT(DATE,'01/31/1900') as since 45BC, January has had 31 days. This caused some times an incorrect result for 2019, and any others where the last Sunday in March is the 31st.

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