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

Question

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

Answer 1

Hi @Sudip Bhatt ,

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.

Answer 2

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))