none
Convert UTC time to Local Time (Daylight Savings Included) in SQL 2005 RRS feed

  • Question

  • Hi,

    I am quoting this question as i didn't find any solid source that could resolve my problem.

    I want to convert UTC converted DateTime (Stored in Database's Table) into Local DateTime in SQL Server 2005 Stored Procedure (Daylight savings included)

    Scenrio:

    Table Design is follow:
    [TimeStamp]* As DateTime -- [Value] As Decimal

    * TimeStamp will store UTC DateTime

    Suppose before 1st half of a year the local time is +4 GMT and in 2nd half the local time is +5 GMT. Above mentioned table is storing values on daily basis.

    example:

    1 jan 2009 5:00 AM will be stored in table as 1 jan 2009 1:00 AM
    1 jul 2009 5:00 AM will be stored in table as 1 jul 2009 00:00 AM

    There is no problem when i insert values in above table. I convert the value in UTC by using GETUTCDATE() function, calculate the difference as values are storing on daily basis.

    Question:

    The problem is in retrieving value again a TimeStamp in Stored Procedure. As i fount only one GETUTCDATE() function which returns only current TimeZoneSettings. Suppose i am in second half and want to retrieve value from first half it will add +5 haour actually it should +4 hour.

    Any help would be great to me.

    Thanks,


    Haseeb Ahmad
    • Moved by Tom PhillipsModerator Monday, June 29, 2009 1:49 PM TSQL Question (From:SQL Server Database Engine)
    Monday, June 29, 2009 5:05 AM

Answers

  • Hi,


    To convert UTC time to local time, try the following code in a stored procedure:


    SELECT
       'Local_Time' =

          CASE

             WHEN month(TimeStamp ())<7 THEN dateadd(hour,4, TimeStamp ())

             ELSE dateadd(hour,5,GETDATE())

          END

    FROM table_name



    If you have any more questions, please let me know.


    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Tuesday, June 30, 2009 8:48 AM
    Moderator

All replies

  • Hi,


    To convert UTC time to local time, try the following code in a stored procedure:


    SELECT
       'Local_Time' =

          CASE

             WHEN month(TimeStamp ())<7 THEN dateadd(hour,4, TimeStamp ())

             ELSE dateadd(hour,5,GETDATE())

          END

    FROM table_name



    If you have any more questions, please let me know.


    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Tuesday, June 30, 2009 8:48 AM
    Moderator
  • I have pondered this myself, and while the if statement works for now, time zones do change albeit not often.  Also, if you want to make it transportable across timezones it becomes a problem to hardcode the time offset, instead, get sql to calculate it.

    This will tell you your current time offset.

    SELECT DATEDIFF (HH, GETUTCDATE(), GETDATE())

    I propose

    SELECT 'Local Time' = dateadd(hour, DATEDIFF (HH, GETUTCDATE(), GETDATE()), 'your UTC Date Value from database')

    Thursday, June 7, 2012 3:07 AM
  • SQL 2016 has new feature - AT TIME ZONE and it would help to resolve this issue.

    Egs:

    declare @OnputDatetime datetime 
    	select @OnputDatetime=@InputDate AT TIME ZONE 'UTC' 					  
    					  AT TIME ZONE 'Eastern Standard Time'
    	return @OnputDatetime

    Regards

    Abdul

    Tuesday, March 10, 2020 6:58 PM