none
Convert Datetime column from UTC to local time in select statement RRS feed

  • Question

  • my database is running in USA PC and my windows and web application connect to that db. i want my application may run in any country but when i will fetch date & time from db that will be displayed as local datetime in my c# application. below sql code not clear to me.

    how to send my client pc datetime offset from my client side to sql server ? or how can i send my timezone info to sql server ?

    how please give me a guideline which show me how to send local time zone info to sql server and sql server use that info to convert utc date & time to local date time.

    SELECT CONVERT(datetime, 
                   SWITCHOFFSET(CONVERT(datetimeoffset, 
                                        MyTable.UtcColumn), 
                                DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
           AS ColumnInLocalTime
    FROM MyTable
    i got the above code from below link.

    https://stackoverflow.com/questions/8038744/convert-datetime-column-from-utc-to-local-time-in-select-statement

    so provide two snippet one for C# to get client timezone info or client timezone offset and another sql server snippet which use client's timezone info to convert utc date & time to local date time.

    thanks

    Saturday, February 8, 2020 7:05 PM

Answers

  • Hi Sudip_inn,

    Thank you for posting here.

    Do you have to use sql statements to convert time?

    Is it possible to store UTC time in the database and convert it to local time after reading it from the client?

    This is the code that converts local time andUTC time.

                DateTime dateNow = DateTime.Now;
                DateTime utcTime = TimeZoneInfo.ConvertTimeToUtc(dateNow);
                Console.WriteLine("The date and time are {0} UTC.",utcTime);
    
                DateTime localTime = utcTime.ToLocalTime();
                Console.WriteLine("The date and time are {0} loacl.", localTime);

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Friday, February 14, 2020 9:05 AM
    Monday, February 10, 2020 1:39 AM

All replies

  • Here is how to get local time zone

    TimeSpan timeSpan = TimeZoneInfo.Local.GetUtcOffset(DateTime.UtcNow);
    var localOffset = $"{(timeSpan < TimeSpan.Zero ? "-" : " + ")}{ timeSpan.ToString(@"hh\:mm")}";
    My time zone is PST which is -8, the above returns -8


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, February 9, 2020 9:56 PM
    Moderator
  • Hi Sudip_inn,

    Thank you for posting here.

    Do you have to use sql statements to convert time?

    Is it possible to store UTC time in the database and convert it to local time after reading it from the client?

    This is the code that converts local time andUTC time.

                DateTime dateNow = DateTime.Now;
                DateTime utcTime = TimeZoneInfo.ConvertTimeToUtc(dateNow);
                Console.WriteLine("The date and time are {0} UTC.",utcTime);
    
                DateTime localTime = utcTime.ToLocalTime();
                Console.WriteLine("The date and time are {0} loacl.", localTime);

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Friday, February 14, 2020 9:05 AM
    Monday, February 10, 2020 1:39 AM
  • i like to know how to send datetimeoffset from client pc to sql server store procedure and sql server store procedure will use that datetimeoffset to convert utc datetime to local datetime.

    if possible please post sample code for both .net & sql server code where it will be shown how to send client pc datetimeoffset to store procedure of sql server and sql server store procedure code which will convert utc datetime to local datetime based on passed client's datetimeoffset 

    thanks

    Tuesday, February 11, 2020 6:34 PM
  • Hi Sudip_inn,

    I took a closer look at that Stack Overflow link.

    Things are not as complicated as you think, there is nothing "send client pc datetimeoffset to store procedure of sql serve". He just stores the time in the database, and then uses the storage structure to convert the time in the database to local time when reading in C#.

    In other words, he converted during the reading process, and my previous suggestion was to read and then convert, which is actually the same, except that I did not use the storage structure.

    If you insist on using a storage structure, you can refer to the link below, but I think this will only complicate the simple problem.

    Call a stored procedure with parameter in c#

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Wednesday, February 12, 2020 9:09 AM