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

naumad

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

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

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;

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