Maximize the power of SQL Prompt with SQL Toolbelt Essentials. Watch now.

Format SQL Fails After 'AT TIME ZONE' Clause

naumadnaumad Posts: 19 Bronze 1
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
Tagged:

Comments

Sign In or Register to comment.