none
Coverting UTC date time to local date time in sql server RRS feed

  • Question

  • please see this link https://sqlserverperformance.wordpress.com/2007/04/25/one-way-to-convert-from-utc-time-to-local-time/

    they use this code.

     Convert a UTC Time to a Local Time
    DECLARE @UTCDate datetime
    DECLARE @LocalDate datetime
    DECLARE @TimeDiff int
    
     Figure out the time difference between UTC and Local time
    SET @UTCDate = GETUTCDATE()
    SET @LocalDate = GETDATE()
    SET @TimeDiff = DATEDIFF(hh, @UTCDate, @LocalDate)
    
     Check Results
    PRINT @LocalDate
    PRINT @UTCDate
    PRINT @TimeDiff
    
     Convert UTC to local time
    DECLARE @DateYouWantToConvert datetime
    DECLARE @ConvertedLocalTime datetime
    
    SET @DateYouWantToConvert = '4/25/2007 18:00'
    SET @ConvertedLocalTime = DATEADD(hh, @TimeDiff, @DateYouWantToConvert)
    
     Check Results
    PRINT @ConvertedLocalTime 

    i do not understand if i do not know client timezone then how could i convert utc date time to local date time.

    Tuesday, January 24, 2017 1:49 PM

Answers

All replies

  • See Dan's solution here

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/dacdfe94-d7b9-4063-84b2-5c2aa31fc895/converting-utc-time-to-local-time-in-sql-server-2008-r2?forum=transactsql


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, January 24, 2017 1:59 PM
    Answerer
  • You can't. 

    If you don't know the time zone of the source date, there is no way to figure out the difference in order to convert it.

    Tuesday, January 24, 2017 2:01 PM
    Moderator
  • i have seen Dan's solution but i am not convinced because he used C# SQL CLR function which is another overhead.

    i want to solve it at sql server level.

    see the above code i posted in this post and tell me that if i do not know client timezone then how could i convert utc date time to local date time ?

    if you know the solution then only answer. thanks

    Tuesday, January 24, 2017 2:03 PM
  • Declare @DT as Datetime = GETUTCDATE()
    Select DATEADD(MILLISECOND,DATEDIFF(MILLISECOND,getutcdate(),GETDATE()),@DT) as UTC_To_LocalTime

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Sudip_inn Tuesday, February 7, 2017 11:15 AM
    Tuesday, January 24, 2017 2:06 PM
    Answerer
  • You are getting some conflicting answers because your question itself contains contradictions.

    You asked "i do not understand if i do not know client timezone then how could i convert utc date time to local date time."

    The contradiction is that local dateTime is not the same as client timezone.

    Local time zone is always the time zone where you run the command.  That is Local time zone on the east coast may be 'EST" while the same command run on the west coast would return "PST"

    In order to avoid always needing TWO fields to store  a dateTime (both client dateTime AND client timeZone), the universal solution is to ALWAYS save all dateTimes to your dataBase in UTC timeZone, then convert from UTC to local time on the client when interacting with the user.

    Monday, January 8, 2018 7:13 PM
  • You can't.

    If you don't know the time zone of the source date, there is no way to figure out the difference in order to convert it.


    We know the time zone of the source data:  UTC
    • Edited by ashleedawg Tuesday, April 17, 2018 9:13 PM
    Tuesday, April 17, 2018 9:13 PM
  • Using the difference between GETUTCDATE() and GETDATE() won't work if the server time is UTC time and/or the query is not run locally.

    The destination time zone would need to be specified.

    Tuesday, April 17, 2018 9:17 PM
  • If you know the source time zone is UTC, you can use Uri's method to convert it to the SERVER local time. 

    However, if you want to convert it to the USER local time, this should be done in the client code, not in SQL Server.

    Wednesday, April 18, 2018 1:02 PM
    Moderator