Recently we needed to convert all dates in our database to UTC format. We've made a mistake not storing them in UTC from the beginning and it led to complicated issues. When you have to support various time zones, there is no other way but to store you dates in UTC.
Return the current UTC date and time: SELECT GETUTCDATE. SQL Server (starting with 2008), Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse SQL Server Functions. Get certified by completing a course today! W 3 s c h o o l s C E R T I F I E D. MySQL UTCDATE returns the current UTC (Coordinated Universal Time) date as a value in 'YYYY-MM-DD' or YYYYMMDD format depending on the context of the function i.e. In a string or numeric context.
Luckily enough SQL Server comes with a handy GETUTCDATE() function, which, as you all have guessed, returns the current UTC date. The only thing you need to know to convert your dates to UTC is the offset between your server's time and UTC. You can do it like this:
For Eastern Standard Time that will be -5. So to convert your dates to UTC you need to do this:
But there is one problem.
Currently the offset between UTC and EST is actually -4, since it's summer and we are now in daylight savings time (EDT). So the above code will substract 4 hours from every date we have. But obviously lots of those past dates are not during daylight savings and you need to substract full five hours from them. Turns out there is really no way around that in SQL Server.
But you can perform the converion outside of SQL. .NET has great built in tools to convert between time zones with daylight savings support. It is really easy to do the conversion in C#:
Then you just need to store it in the database and you are all set. You can even create a CLR stored procedure out of that method. Hopefully all of the above will save you some time.
Max Al Farakh
Max is a co-founder/CTO of Jitbit with tons of experience in customer service.