Format SQL Fails After 'AT TIME ZONE' Clause

naumad

SQL Prompt 8.0.7.1917
SQL Server 2016 SP1 13.0.4422.0

On the following SQL, Format SQL fails to format with error "Error Inserting Semicolons". The result after this error is the column alias 'scandate_local' being changed to 'lst.scandate_local'. Occasionally, format will change line 5 to read ', CONVERT(Time, lpr.scandatetimeutc AS AT TIME ZONE 'UTC' AT TIME ZONE dlr.tz_name) AS scantime_local'.


SELECT
lpr.m_id
, CONVERT(Date, lpr.scndatetimeutc AT TIME ZONE 'UTC' at time zone dlr.tz_name) AS scandate_local
, CONVERT(Time, lpr.scndatetimeutc AT TIME ZONE 'UTC' AT TIME ZONE dlr.tz_name) AS scantime_local
, ROW_NUMBER() OVER (PARTITION BY dlr.dlr, lpr.scndatetimeutc, lpr.vin, lpr.scnname, lpr.lp
ORDER BY lpr.scndatetimeutc DESC, lpr.audit_moddatetimeutc DESC, lpr.audit_createdatetimeutc DESC) AS ranknum
FROM dbo.vw_dlpr04_lprscan AS lpr
INNER JOIN dbo.tmp_dlpr04_dealer AS dlr ON lpr.dlr = dlr.dlr
LEFT JOIN rpt.dlpr04_list lst ON lpr.dlr = lst.dlr
AND lpr.scndatetimeutc = lst.scandatetime_utc
AND lpr.scnname = lst.scn_name
AND lpr.vin = lst.vin

FabiolaB

Hi @naumad,

Thanks for letting us know.

In order to replicate the issue here we would need your SQLPrompt log file and also your Formatting Styles file. Could you please attach them to this discussion?

Best regards,
Fabiola

naumad

the style file extension (.log) needs to be removed - the upload tool wouldn't let me use this file without putting a 3 character extension on the file

FabiolaB

Hi @naumad,

Thank you for the files. We have created a bug (reference: SP-6560) to investigate the problem and to track it.

Best regards,
Fabiola