TSQL: How to convert local time to UTC? (SQL Server 2008)

sql server convert local time to utc daylight savings
sql convert string to utc date
sql server convert utc to specific timezone
convert datetimeoffset to datetime sql
sql server getdate timezone
sql server daylight saving aware
ssis convert utc to local time
convert utc to pst in sql server

We are dealing with an application that needs to handle global time data from different time zones and daylight savings time settings. The idea is to store everything in UTC format internally and only convert back and forth for the localized user interfaces. Does the SQL Server offer any mechanisms for dealing with the translations given a time, a country and a timezone?

This must be a common problem, so I'm surprised google wouldn't turn up anything usable.

Any pointers?

7 years passed and... 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. More info here: https://msdn.microsoft.com/en-us/library/mt612795.aspx

Convert Local Time to UTC – SQLServerCentral, The idea is to store everything in UTC format internally and only convert back and forth for the localized user interfaces. Does the SQL Server offer any  You can use my SQL Server Time Zone Support project to convert between IANA standard time zones, as listed here. UTC to Local is like this: SELECT Tzdb.UtcToLocal('2015-07-01 00:00:00', 'America/Los_Angeles') Local to UTC is like this: SELECT Tzdb.LocalToUtc('2015-07-01 00:00:00', 'America/Los_Angeles', 1, 1) The numeric options are flag for

This works for dates that currently have the same UTC offset as SQL Server's host; it doesn't account for daylight savings changes. Replace YOUR_DATE with the local date to convert.

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

TSQL: How to convert local time to UTC? (SQL Server 2008 , Convert Local Time to UTC – Learn more on the SQLServerCentral forums. Home · Forums · SQL Server 2008 · T-SQL (SS2K8); Convert Local Time to UTC For better, quicker answers on T-SQL questions, click on the For better, quicker answers on SQL Server performance related questions, click on  How to convert UTC to LOCAL time conversion in SQL Server – Querychat We often come across situations to convert date and time to any other time zone for end users convenience. Time zone conversion becomes very important when we have users from all over the globe, to make day to day analysis smooth and uniform all over the globe.

While a few of these answers will get you in the ballpark, you cannot do what you're trying to do with arbitrary dates for SqlServer 2005 and earlier because of daylight savings time. Using the difference between the current local and current UTC will give me the offset as it exists today. I have not found a way to determine what the offset would have been for the date in question.

That said, I know that SqlServer 2008 provides some new date functions that may address that issue, but folks using an earlier version need to be aware of the limitations.

Our approach is to persist UTC and perform the conversion on the client side where we have more control over the conversion's accuracy.

AT TIME ZONE (Transact-SQL), Question. We are dealing with an application that needs to handle global time data from different time zones and daylight savings time settings. The idea is to  First, understand that getdate () returns the local date and time of the server - not of the person running the query. If the server's time zone is set to UTC, then it will indeed return the UTC time. Since you are running on SQL 2016, and you are asking for the UTC time converted to Pacific time, I suggest you use the built-in AT TIME ZONE

SQL Server 2008 has a type called datetimeoffset. It's really useful for this type of stuff.

http://msdn.microsoft.com/en-us/library/bb630289.aspx

Then you can use the function SWITCHOFFSET to move it from one timezone to another, but still keeping the same UTC value.

http://msdn.microsoft.com/en-us/library/bb677244.aspx

Rob

GETUTCDATE (Transact-SQL), Points in time that are in this gap are converted with the offset after DST change. Copy. /* Moving to DST in "Central European Standard  Arguments. inputdate Is an expression that can be resolved to a smalldatetime, datetime, datetime2, or datetimeoffset value.. timezone Name of the destination time zone. SQL Server relies on time zones that are stored in the Windows Registry.

You can use my SQL Server Time Zone Support project to convert between IANA standard time zones, as listed here.

UTC to Local is like this:

SELECT Tzdb.UtcToLocal('2015-07-01 00:00:00', 'America/Los_Angeles')

Local to UTC is like this:

SELECT Tzdb.LocalToUtc('2015-07-01 00:00:00', 'America/Los_Angeles', 1, 1)

The numeric options are flag for controlling the behavior when the local time values are affected by daylight saving time. These are described in detail in the project's documentation.

TSQL: How to convert local time to UTC? (SQL Server 2008), The database time zone offset is not included. This value For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions CAST and CONVERT (Transact-SQL) For SQL Server 2016 and newer, and Azure SQL Database, use the built in AT TIME ZONE statement. For older editions of SQL Server, you can use my SQL Server Time Zone Support project to convert between IANA standard time zones, as listed here. UTC to Local is like this: SELECT Tzdb.UtcToLocal('2015-07-01 00:00:00', 'America/Los_Angeles')

SQL Server: how to convert datetime to UTC, TSQL: How to convert local time to UTC? (SQL Server 2008) - sql-server. Returns the current database system timestamp as a datetime value. The database time zone offset is not included. This value represents the current UTC time (Coordinated Universal Time). This value is derived from the operating system of the computer on which the instance of SQL Server is running.

AT TIME ZONE, 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  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.

Handle conversion between time zones in SQL Server, One of my favourite, but often unappreciated, features of SQL 2008 was the Database servers often run in UTC, because it's simply easier to not have SELECT CONVERT(datetime,'20160101 00:00') AT TIME ZONE 'Cen. Usually the conversion to the local time is handled at the application level. But this time you need a custom data export to another in house application and the date and time data should be expressed as local time. In this tip I'll explain a straightforward way to convert the UTC date and time to local time using a CLR scalar function. Solution

Comments
  • I have my mssql-server linked to a mysql-server. I wonder if it is possible to run mysql CONVERT_TZ(time,srczone,dstzone) on the queries :-) Strange this function is missing; it is built into linux.
  • @BuschnicK See my answer below. Actually I think you can accept it even so it's easier for others to find.
  • The short answer: there is no built-in way to do this before SQL Server 2016, so it will require custom code on earlier versions.
  • The medium answer: all time offset functionality before SQL Server 2016 only worked with absolute offsets, with no support for the variable offsets that occur in most time zones due to Daylight Saving Time. Queries do not have any way of accessing time offsets except for whatever happens to be the current offset of the server's local time, which is useless for trying to automate conversion.
  • No longer working on this - not at that project, not at SQL Server, not at the same company and not even in the same country ;-) So it won't help me, but I'll upvote it for people finding this question now.
  • @BuschnicK yeah, I figure, but I got here looking for a solution to the same problem you had so I decided to post an aswer now that there is a real solution :D
  • The question is for SQL Server 2008. Please update the question if you accept this answer. Thx
  • To convert to UTC you can do 'AT TIME ZONE 'UTC'.
  • @Piotr Owsiak: Yea, but it assumes that the input time is UTC, which is pointless if you want to convert from local time to UTC... So 7 years passed, and they still don't think it's worth handling this properly...
  • Thanks, this is a good idea but it only works for exactly one timezone - the local machine's. We need it to work for arbitrary timezones though...
  • This doesn't account for daylight savings time