How can we help you today? How can we help you today?

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.


sridharv86
0

Comments

3 comments

Sort by
  • 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')))

    sridharv86
    0
  • Russell D
    Hi @sridharv86 thanks for the update, glad you got it sorted.
    Russell D
    0
  • 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

    Nitin1904
    0

Add comment

Please sign in to leave a comment.