none
Converting UTC Time To Local Time in T-Sql RRS feed

Answers

  • Try the below script

     
    Declare @Input_Utc_Date as Datetime = '2012-12-05 05:12:51.107' -- GETUTCDATE()
    Select DATEADD(MILLISECOND,DATEDIFF(MILLISECOND,getutcdate(),GETDATE()),@Input_Utc_Date) as UTC_To_LocalTime
    ------------------------------
    ----------------------------


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, December 5, 2012 5:11 AM
  • Hi All,

    Thanks for Your Reply..

    This is Solution:

    DECLARE @V_UTC_DATETIME DATETIME
    SET @V_UTC_DATETIME='2012-12-03T08:48:52'
    SELECT @V_UTC_DATETIME AS UTC_DATETIME,DATEADD(hh,((DATEDIFF(ss,@V_UTC_DATETIME,GETDATE())-DATEDIFF(ss,@V_UTC_DATETIME,GETUTCDATE()))/3600), @V_UTC_DATETIME) AS LOCAL_DATETIME


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Wednesday, December 5, 2012 6:02 AM

All replies

  • Try the below script

     
    Declare @Input_Utc_Date as Datetime = '2012-12-05 05:12:51.107' -- GETUTCDATE()
    Select DATEADD(MILLISECOND,DATEDIFF(MILLISECOND,getutcdate(),GETDATE()),@Input_Utc_Date) as UTC_To_LocalTime
    ------------------------------
    ----------------------------


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, December 5, 2012 5:11 AM
  • Hi,

    getdate() would return the server time

    again which may be located anywhere on earth or may be incorrect.

    So local datetime from UTC time can be safely calculated by

    adding/subtracting (using datetime calculations, and not the numeric addition/subtraction) the time zone difference for your zone

    Regards.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help , or you may vote-up a helpful post



    • Edited by murtazagandhi Wednesday, December 5, 2012 5:52 AM edit
    • Proposed as answer by murtazagandhi Wednesday, December 5, 2012 5:58 AM
    Wednesday, December 5, 2012 5:50 AM
  • Hi All,

    Thanks for Your Reply..

    This is Solution:

    DECLARE @V_UTC_DATETIME DATETIME
    SET @V_UTC_DATETIME='2012-12-03T08:48:52'
    SELECT @V_UTC_DATETIME AS UTC_DATETIME,DATEADD(hh,((DATEDIFF(ss,@V_UTC_DATETIME,GETDATE())-DATEDIFF(ss,@V_UTC_DATETIME,GETUTCDATE()))/3600), @V_UTC_DATETIME) AS LOCAL_DATETIME


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Wednesday, December 5, 2012 6:02 AM
  • Hello,
     
    I don't know if you have Daylight saving in Melbourne.
    If yes, be careful because you calculate the difference at the current
    date, which can be different from the difference at the date you convert.
    In a similar situation, I used a simple table filled with the daylight
    saving change dates for the next years.
     

    Fred
    Wednesday, December 5, 2012 7:25 AM
  • Hi Fred,

    I hope the above Expression will give correct result even for Daylight Saving. In Melbourne we have daylight Saving. At current we have 11 Hours difference.

    http://www.vic.gov.au/daylight-saving-in-victoria.html


    Thanks Shiven:) If Answer is Helpful, Please Vote



    Wednesday, December 5, 2012 7:32 AM
  • Yes, but I meen, what is the result if you write
    SET @V_UTC_DATETIME = '2012-05-03T08:48:52' ?
    Or any date in the other daylight saving interval than the current date.
    Is it correct also ?
     

    Fred
    Wednesday, December 5, 2012 7:43 AM
  • Hi,

    I have implemented this logic in ETL & that is daily load for one day Time windows.

    May be It will not give correct result If I ran the ETL for Back date. For Example: Daylight Saving Started in Oct month..  In Nov If I will try to load data for July moth then May be It will give difference 11 Hours But in July difference was 10 Hours.

    Any suggestion to make this dynamic (To handle Day light Saving also). Note I am using SQL DB 2005 & SSIS 2005.


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Wednesday, December 5, 2012 7:52 AM
  • Here is a simplified code.

    Sorry if my english translation is not correct. Values are from France.

    CREATE SCHEMA [sch] AUTHORIZATION [dbo]
    GO
    CREATE TABLE [sch].[DAYLIGHT_SAVINGS](
    	[ds_start_date] [datetime] NOT NULL,
    	[ds_end_date] [datetime] NOT NULL,
     CONSTRAINT [PK_DAYLIGHT_SAVINGS] PRIMARY KEY CLUSTERED 
    (
    	[ds_start_date] ASC,
    	[ds_end_date] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    INSERT  sch.DAYLIGHT_SAVINGS(ds_start_date, ds_end_date)
    VALUES  ('20090329 01:00:00.000',	'20091025 01:00:00.000'), 
            ('20100328 01:00:00.000', '20101031 01:00:00.000'),
            ('20110327 01:00:00.000', '20111030 01:00:00.000'),
            ('20120325 01:00:00.000', '20121028 01:00:00.000'),
            ('20130331 01:00:00.000', '20131027 01:00:00.000'),
            ('20140330 01:00:00.000', '20141026 01:00:00.000'),
            ('20150329 01:00:00.000', '20151025 01:00:00.000'),
            ('20160327 01:00:00.000', '20161030 01:00:00.000'),
            ('20170326 01:00:00.000', '20171029 01:00:00.000'),
            ('20180325 01:00:00.000', '20181028 01:00:00.000'),
            ('20190331 01:00:00.000', '20191027 01:00:00.000'),
            ('20200329 01:00:00.000', '20201025 01:00:00.000')
    GO
    DECLARE @utc_offset AS INT = 1
    DECLARE @utc_date AS DATETIME = '20120505 08:12'
    
    SELECT DATEADD( HOUR, 
                    @utc_offset + COALESCE((SELECT 1 FROM sch.DAYLIGHT_SAVINGS AS DS WHERE   @utc_date >= DS.ds_start_date AND @utc_date < DS.ds_end_date), 0),
                    @utc_date)


    Fred

    Wednesday, December 5, 2012 8:19 AM
  • Hi Kumar,
    I’m agree with Murtazagandhi, getdate() returns different values depends on your SQL Server current system timestamp.
    In your query, it seems that the query cannot convert your current UTC time to local Melbourne time according to my test. 
    Since there is daylight saving in Melbourne, please try below query:
    DECLARE @V_UTC_DATETIME DATETIME
    SET @V_UTC_DATETIME=getutcdate()
    if convert(int,month(getdate()))>10
    SELECT @V_UTC_DATETIME AS UTC_DATETIME,
    DATEADD(hh, DATEPART(hh, GETDATE() - GETUTCDATE()) + 11, GETUTCDATE()) AS LOCAL_DATETIME
    else
    SELECT @V_UTC_DATETIME AS UTC_DATETIME,
    DATEADD(hh, DATEPART(hh, GETDATE() - GETUTCDATE()) + 10, GETUTCDATE()) AS LOCAL_DATETIME

    Since I know little about daylight saving in Melbourne, please modify the condition in the query as your requirement.

    Iric Wen
    TechNet Community Support

    Wednesday, December 5, 2012 8:21 AM
    Moderator
  • Sample usage:

    SELECT    Getdate=GETDATE()    ,SysDateTimeOffset=SYSDATETIMEOFFSET()    ,SWITCHOFFSET=SWITCHOFFSET(SYSDATETIMEOFFSET(),0)    ,GetutcDate=GETUTCDATE() GO

    Returns: Getdate SysDateTimeOffset   SWITCHOFFSET    GetutcDate 2013-12-06 15:54:55.373 2013-12-06 15:54:55.3765498 -08:00  2013-12-06 23:54:55.3765498 +00:00  2013-12-06 23:54:55.373

    Saturday, December 7, 2013 12:15 AM
  • The problem with all "solutions" which use GETUTCDATE() function is they work only if the given datetime was from the period of the year in which it was the same daylight saving time offset as current time. Unfortunately, you will get wrong results if input datetime is from e.g. 6months before now, or you just switched daylight saving time.

    AFAIK, there are only two possibilities to get exact result:

    1) implement the DST logic of your country in the TSQL

    2) use the DST logic for your country already built-in the OS, through the SQL CLR function.

    The second solution is much much easier to implement, and I suggest you to choose that.

    Here is one example: http://www.mssqltips.com/sqlservertip/2339/converting-utc-to-local-time-with-sql-server-clr/

    Best regards,

    Vedran Kesegić

    Saturday, December 7, 2013 12:49 AM
  • GMT has not existed for decades, but people still say it!  Do you also say Centigrade instead of Celsius? GMT is now a time zone in the UK. 

    In full ANSI/ISO Standard SQL, local time zones are built into the temporal data types. 

    In T-SQL the best way is  look-up table with the time zones and the daylight saving time adjustments.  Hide it in a VIEW. 

     

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, December 7, 2013 6:07 PM
  • This doesn't work if the datetime you want to convert is in a different "Daylight status" then your current status.  Meaning if you want to convert a datetime from last December (in Standard [not Daylight]), and the current date is June (in Daylight), you'll be applying the wrong offset.  This is even true on the day of change if using a time before the change.
    Monday, July 15, 2019 9:18 PM