Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server - Convert date field to UTC

I have recently updated my system to record date/times as UTC as previously they were storing as local time.

I now need to convert all the local stored date/times to UTC. I was wondering if there is any built in function, similar to .NET's ConvertTime method?

I am trying to avoid having to write a utility app to do this for me.

Any suggestions?

like image 629
James Avatar asked Apr 23 '10 16:04

James


People also ask

How do you convert date to UTC format?

The ToUniversalTime method converts a DateTime value from local time to UTC. To convert the time in a non-local time zone to UTC, use the TimeZoneInfo. ConvertTimeToUtc(DateTime, TimeZoneInfo) method. To convert a time whose offset from UTC is known, use the ToUniversalTime method.

How do I get UTC date in SQL?

SQL Server GETUTCDATE() Function The GETUTCDATE() function returns the current database system UTC date and time, in a 'YYYY-MM-DD hh:mm:ss. mmm' format.

Is SQL datetime UTC?

Regarding serialization and moving data between computers, there is no need to bother, as the datetime is always UTC.

How do you convert PST to UTC in SQL?

2 Answers. SELECT GETDATE() AS CurrentTime, GETUTCDATE() AS UTCTime. SELECT DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), YOUR_DATE);


1 Answers

I do not believe the above code will work. The reason is that it depends upon the difference between the current date in local and UTC times. For example, here in California we are now in PDT (Pacific Daylight Time); the difference between this time and UTC is 7 hours. The code provided will, if run now, add 7 hours to every date which is desired to be converted. But if a historical stored date, or a date in the future, is converted, and that date is not during daylight savings time, it will still add 7, when the correct offset is 8. Bottom line: you cannot convert date/times properly between time zones (including UTC, which does not obey daylight savings time) by only looking at the current date. You must consider the date itself that you are converting, as to whether daylight time was in force on that date. Furthermore, the dates at which daylight and standard times change themselves have changed (George Bush changed the dates during his administration for the USA!). In other words, any solution which even references getdate() or getutcdate() does not work. It must parse the actual date to be converted.

like image 51
Roderick Llewellyn Avatar answered Sep 21 '22 19:09

Roderick Llewellyn