question

SudipBhatt-9737 avatar image
0 Votes"
SudipBhatt-9737 asked DanielAdeniji commented

SQL Server: How to save date and time in UTC format

I am developing a small issue management system where all our own company user from our various branches will post issues. from the server side i will call a store proc which will insert issues info into table and i will also send server side date & time to store proc and i want to know how could i convert server side date & time to UTC date & time and store in sql server table.

second scenario. when user read data then again i will send user time zone info to store proc and store proc will convert utc date & time to local time as per user time zone info.

1) so please show me how could i convert server side date & time to UTC date & time and store in sql server table
2) how to convert UTC date & time to user local time as per user's time zone info in my store proc.

please answer for my 2 points. thanks

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


If you mean the current server-side time, then you can use GETUTCDATE( ) instead of GETDATE( ). Then no conversion is needed.

1 Vote 1 ·
MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered ErlandSommarskog commented

Hi @SudipBhatt-9737,

You could try with GETUTCDATE which returns the current database system timestamp as a datetime value.

If they're all local to you, then here's the offset:

 SELECT GETDATE() AS CurrentTime, GETUTCDATE() AS UTCTime

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

and you should be able to update all the data using:

 UPDATE SomeTable
    SET DateTimeStamp = DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), DateTimeStamp)

Actually there's this new SQL Server 2016 feature that does exactly what you need.
It is called AT TIME ZONE and it converts date to a specified time zone considering DST (daylight saving time) changes.

 USE AdventureWorks2016;
 GO
    
 SELECT SalesOrderID, OrderDate,
     OrderDate AT TIME ZONE 'Pacific Standard Time' AS OrderDate_TimeZonePST,
     OrderDate AT TIME ZONE 'Central European Standard Time' AS OrderDate_TimeZoneCET
 FROM Sales.SalesOrderHeader;

In addition, you could also use the function SWITCHOFFSET to move it from one timezone to another, but still keeping the same UTC value.

 SELECT CONVERT(datetime, 
                SWITCHOFFSET(CONVERT(datetimeoffset, 
                                     MyTable.UtcColumn), 
                             DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
        AS ColumnInLocalTime
 FROM MyTable
    
 SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), MyTable.UtcColumn) 
        AS ColumnInLocalTime
 FROM MyTable

 select CONVERT(datetime, SWITCHOFFSET(dateTimeField, DATEPART(TZOFFSET, 
 dateTimeField AT TIME ZONE 'Eastern Standard Time')))

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Note that

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

Assumes that YOUR_DATE is in the same time zone as the computer where SQL Server is running. Maybe this is case for Sudip, but that was not how I interpreted his question.

So if the SQL Server machine is in IST, that is +05:30, and the user is CEST (+02:00) and sends in a time stamp like 22:57 in that time zone, the operation above will not produce a time UTC. The result should be 20:57, but the above be 17:37.

0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered DanielAdeniji commented

When you store the data, do you know the time zone of the data? If you do not, you can of course not change the time zone... So the client does not only need to pass the time stamp, but it must also pass the time-zone offset from UTC. Although, an alternative could possibly be that there is a configuration where you can look up a client's time-zone offset.

The same applies when you return the data. You could convert from UTC to the user's time zone in SQL Server. However, you need to pass the user's time zone to SQL Server, because SQL Server does not know this. Although, again, it could look up the time-zone offset in a configuration table that holds user informtation.

This leads to the observation: maybe it is better to put the onus on the client to make sure the time is in UTC?

So far I have been assuming that you will get the timestamps from the outside, but often one takes the time from within SQL Server, and in this case it is simple: use the system function sysutcdatetime(). You still need to know the user's time-zone offset to return the time in the user's time zone. Once you have that information, you can use the function switchoffset as in this example:

 DECLARE @tzoffset char(6) = '-04:00'
 SELECT convert(datetime2(3), switchoffset(getdate(), @tzoffset))


· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SELECT convert(datetime2(3), switchoffset(getdate(), @tzoffset))

what is datetime2(3) what is meaning of (3) here ? people use datetime datatype but what special meaning it will carry if i use datetime(0)

Thanks

0 Votes 0 ·

Hi @SudipBhatt-9737,

Please refer the syntax of datetime2 from below:

 datetime2 [ (fractional seconds precision) ].

For example:

  SELECT CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS 'datetime2(7)' 
 ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(3)) AS 'datetime2(3)'  

Output:
datetime2(7) datetime2(3)

2007-05-08 12:35:29.1234567 2007-05-08 12:35:29.123

Best regards
Melissa

0 Votes 0 ·

datetime2(n) - Microsoft refers to n as the scale, although I more think of it as the precision. In any case, it is the number of fractions after the seconds. datetime2(0) means a precision on one second, datetime2(3) gives you an accuracy of 1 ms, which is good for times captured inside SQL Server. The default for datetime2 is datetime2(7), or 100 ns, something you very rarely have any practical need for.

Not matter the "scale", datetime2 has a range from 0001-01-01 to 9999-12-31.

datetime has an accuracy of 3.33 ms, and has a range from 1753-01-01 to 9999-12-31.

0 Votes 0 ·

Erland:-

I am thinking rather than using getdate(), you want to use getutcdate().

getutcdate as you want to employ the UTC date.

And, not getdate() where timezone is not kept ( immaterial).

  DECLARE @tzoffsetPST char(6) 
     
  set @tzoffsetPST = '-07:00'
    
  SELECT  
    
            [timeZoneOffsetOnServer]
                 = FORMAT
                     (
                           SYSDATETIMEOFFSET()
                         , 'zzz'
                     ) 
    
            , [tsUsingGetDate]
             = convert
             (
                   datetime2(3)
                 , switchoffset
                     (
                         getdate()
                         , @tzoffsetPST
                     )
             )
    
             , [tsUsingGetUtcDate]
                 = convert
                   (
                           datetime2(3)
                         , switchoffset
                           (
                               getutcdate()
                             , @tzoffsetPST
                           )
                   )

0 Votes 0 ·