locked
Convert time Zone RRS feed

  • Question

  • Hi Experts

    I have a column storing date & time in Central Time . Now due to some financial requirement its required to convert the Central time based data into UTC time zone . For generating the financial spreadsheet i have already written & tested store procs.

    Could any please help me in changing the time zone using Convert/Cast or any simplified way in the retrieval queries itself .

    Thanks in Advance

    Priya

    Monday, May 4, 2015 5:30 AM

Answers

  • example with AdventureWorks database

    SELECT SOH.SalesOrderID,DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), SOH.OrderDate) OrderDATeUTC,SOH.OrderDate From sales.SalesOrderHeader SOH;


    Satheesh
    My Blog | How to ask questions in technical forum

    • Proposed as answer by Eric__Zhang Monday, May 18, 2015 3:17 AM
    • Marked as answer by Eric__Zhang Tuesday, May 19, 2015 7:41 AM
    Monday, May 4, 2015 5:36 AM
  • Unfortunately, SQL Server doesn't provide effective method to change time zone of date time unlike .NET which can use .NET framework globalization file to get time according timezone.

    but you can always create your own time zone table to get the offset and then use DATEADD function to get the appropriate time zone. 

    Alternatives, 

    You can either use CLR function to get this work done or you can install timezone support from here

    SELECT Tzdb.UtcToLocal('2015-07-01 00:00:00', 'Asia/Kuwait')

    If you are fine with ignoring daylight saving time then below code can also work for you because CST = UTC - 5 hours 

    DECLARE @MyDate DATETIME = '2015/05/02 06:54:00' 
    
    SELECT DATEADD(HH,5,@MyDate)

    Hope this will help


    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.


    My Profile on Microsoft ASP.NET forum



    • Edited by Anuj Tripathi Monday, May 4, 2015 11:29 AM
    • Proposed as answer by Eric__Zhang Monday, May 18, 2015 3:17 AM
    • Marked as answer by Eric__Zhang Tuesday, May 19, 2015 7:41 AM
    Monday, May 4, 2015 5:42 AM

All replies

  • example with AdventureWorks database

    SELECT SOH.SalesOrderID,DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), SOH.OrderDate) OrderDATeUTC,SOH.OrderDate From sales.SalesOrderHeader SOH;


    Satheesh
    My Blog | How to ask questions in technical forum

    • Proposed as answer by Eric__Zhang Monday, May 18, 2015 3:17 AM
    • Marked as answer by Eric__Zhang Tuesday, May 19, 2015 7:41 AM
    Monday, May 4, 2015 5:36 AM
  • Unfortunately, SQL Server doesn't provide effective method to change time zone of date time unlike .NET which can use .NET framework globalization file to get time according timezone.

    but you can always create your own time zone table to get the offset and then use DATEADD function to get the appropriate time zone. 

    Alternatives, 

    You can either use CLR function to get this work done or you can install timezone support from here

    SELECT Tzdb.UtcToLocal('2015-07-01 00:00:00', 'Asia/Kuwait')

    If you are fine with ignoring daylight saving time then below code can also work for you because CST = UTC - 5 hours 

    DECLARE @MyDate DATETIME = '2015/05/02 06:54:00' 
    
    SELECT DATEADD(HH,5,@MyDate)

    Hope this will help


    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.


    My Profile on Microsoft ASP.NET forum



    • Edited by Anuj Tripathi Monday, May 4, 2015 11:29 AM
    • Proposed as answer by Eric__Zhang Monday, May 18, 2015 3:17 AM
    • Marked as answer by Eric__Zhang Tuesday, May 19, 2015 7:41 AM
    Monday, May 4, 2015 5:42 AM
  • Hello! You can try this:

    SELECT DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), GETDATE());

    You can replace GETDATE() with your actual variable or column

    HTH


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Monday, May 4, 2015 6:02 AM