Looking for datetimeoffset function RRS feed

  • Question

  • Hi:

    Our situation:

    We have clients who access our system from all over the world. We need to keep track of their local time and offset hour.  The stored date will be displayed back to client in 3 possible formats: (1) in client local time, or (2) in viewer's local time.   For example, a client in UK performs a transaction on our system, our system record his local time and offset hours,  when this cilent view the data it will display in his local time.  However, a report viewer in California, USA, will want to know the event in his California time so we will also need to conver the data to California, USA time for display in report.  (3) other report viewer, want to see the data in UTC format.

    SQL functions we need:

    1.   We know the local time in datetime format and offsethour in int format, how to put this into datetimeoffset format column in table?

    2.   When we need the local time data, how to convert the datetimeoffset to datetime for display.

    3.    When we need to display the data in UTC format, how to convert datetimeoffset to UTC time in datetime format?

    4.   For viewer who wants to see data in their time such as California USA time, we can convert the datetimeoffset first to UTC then subtract the offset hour.  So do we have a Microsoft function that will convert datetimeoffset data minus a offset hour to a viewers time in datetime format?  How to retrieve the offset hour from datetimeoffset?

    It appears to me that Microsoft did not develop any functions to support the use of datetimeoffset data type.  It will be easy for us just to create two columns to store the information (local datetime and offsethour) so that we can use available function to process the data.



    Thursday, April 7, 2011 2:23 PM


  • I would store all dates as UTC as it makes it a lot easier, and only offset when displaying and passing parameters to sql.



    SET @dt = '20110101 17:00'
    SELECT SWITCHOFFSET(@dt, '+04:00')
    --'2011-01-01 21:00:00.0000000 +04:00'

    • Marked as answer by KJian_ Thursday, April 14, 2011 7:47 AM
    Thursday, April 7, 2011 3:04 PM