-1

I'm currently migrating my system to Azure SQL so I need to convert the time zone from the UTC standard to the local time zone when using GETDATE(). This is straight forward with 'AT TIME ZONE..', however we also have a couple of clients who host themselves on machines running SQL Server pre-2016.

Is there any way in SQL that one function could be setup to get the time zone that'd work for both?

4c74356b41
  • 69,186
  • 6
  • 100
  • 141
Andrew Dally
  • 1

2 Answers2

0

In Azure SQL Database, the regional settings of the database are set to UTC by default. It is also advisable to store dates and times in UTC format on our on-premises SQL Server instances, and handle all time zone calculations at the presentation layer.

Sometimes though, it may be necessary to query data directly and see what the local date and time is, or calculate the date and time in another region. For this use-case we can use the AT TIME ZONE hint, which was introduced in SQL Server 2016.

For computers running pre-SQL Server 2016, you can make you date function query which database engine is running and then use GETDATE() for those SQL Server versions.

SELECT SERVERPROPERTY ('edition')
Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
  • Thanks for the advice, I do understand that storing in UTC is best practice; but not ideal for our application as the job to convert all dates is too great and currently looking for a quick fix. That server property is very useful, in the end I wrote some dynamic SQL to avoid compile issues on the different versions, will post separately – Andrew Dally Sep 16 '19 at 09:20
0

Here it is, please see Alberto Morillo's answer for reference.

DECLARE @CurrentDateTime DATETIME,
@RawSQL NVARCHAR(1000)

IF ((SELECT CAST(SERVERPROPERTY('Edition') AS SYSNAME)) = 'SQL Azure')
BEGIN
    SET @RawSQL = 'SET @CurrentDateTimeOUT = CONVERT(DATETIME, CONVERT(datetimeoffset, GETDATE()) AT TIME ZONE ''GMT Standard Time'') '
END
ELSE
BEGIN
    SET @RawSQL = 'SET @CurrentDateTimeOUT = GETDATE() '
END
EXEC sp_executesql @RawSQL, N'@CurrentDateTimeOUT datetime OUTPUT', @CurrentDateTimeOUT=@CurrentDateTime OUTPUT

SELECT @CurrentDateTime
Andrew Dally
  • 1