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

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.



  • sridharv86sridharv86 Posts: 3 New member

    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')))

  • Hi @sridharv86 thanks for the update, glad you got it sorted.
    Have you visited our Help Centre?
  • Nitin1904Nitin1904 Posts: 1 New member
    edited October 8, 2020 7:06AM
    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

Sign In or Register to comment.