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

Bug: Format SQL fails for "at time zone" (v8.0.5.1758)

When formatting a select statement with the following column:

CONVERT(Time, lpr.scandatetimeutc AT TIME ZONE 'UTC' AT TIME ZONE dlr.tz_name) AS time_local

SQL Prompt adds 'AS' into the column like this:

CONVERT(Time, lpr.scandatetimeutc AS AT TIME ZONE 'UTC' AT TIME ZONE dlr.tz_name) AS time_local
naumad
0

Comments

3 comments

Sort by
  • FredericoJ
    Hi @naumad,

    Thank you for reporting this.

    We've been unable to replicate this using SQL Prompt 8.0.5.1758.

    Can you please provide the full Select statement (or a longer SQL snippet) where the issue occurs?

    Best regards,

    Frederico
    FredericoJ
    0
  • naumad
    SQL Prompt 8.0.5.1758
    Prompt will change Line 6 of this statement by adding 'as' before 'at time zone':

    SELECT
    *
    FROM (
    SELECT
    CONVERT(Date, lpr.scandatetimeutc AT TIME ZONE 'UTC' at time zone dlr.tz_name) AS scandate_local
    , CONVERT(Time, lpr.scandatetimeutc AT TIME ZONE 'UTC' AT TIME ZONE dlr.tz_name) AS scantime_local
    FROM mdlwhs_reporting.dbo.vw_test1 AS lpr
    INNER JOIN mdlwhs_reporting.dbo.tmp_test2 AS dlr ON lpr.dealerinfo_dealercode = dlr.dealerinfo_dealercode
    ) AS sub;
    naumad
    0
  • FredericoJ
    Thanks @naumad.

    We can now recreate the problem locally and I have created a bug (reference: SP-6448) to further investigate.

    We will let you know as soon as we have more information.

    Best regards,

    Frederico
    FredericoJ
    0

Add comment

Please sign in to leave a comment.