Invalid Sql error - SQL Server "AT TIME ZONE" function

sridharv86

Getting an error when opening the commit tab in Sql Source Control. Recent commit we made is to add a calculated column to a table which is using a function "AT TIME ZONE" which is introducted in SQL Server 2016.

[last_call_interaction_date_time_CST] AS (CONVERT([datetime], AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time',(0)))

I am using source control v7 and it is a SQL Server 2017 database.

sridharv86

In the initial commit, I see that the complete sql did not commit properly from the history as you can see below

[last_call_interaction_date_time_CST] AS (CONVERT([datetime], AT TIME ZONE AT TIME ZONE ,(0)))

I fixed the issue by correcting the sql like below and it worked.

[last_call_interaction_date_time_CST] AS (CONVERT([datetime],(([last_call_interaction_date_time_UTC] AT TIME ZONE 'UTC') AT TIME ZONE 'Central Standard Time')))

Russell D

Hi @sridharv86 thanks for the update, glad you got it sorted.

Nitin1904

Getting an error while using the AT TIME ZONE function, the SQL change Automation not supporting this function and i have to convert the query in dynamic one to make it workable. 

Here is the version detail i am using.
SQL Change Automation Version : 4.3.20211.21565

Visual Studio : 2019

SQL Server : 2017

Error : SQL80001 Incorrect syntex near 'AT TIME ZONE'

sample statement  : SELECT [TimeStamp] AT TIME ZONE 'UTC' AS UTC_Time FROM TableA