What are the challenges you face when working across database platforms? Take the survey

Format SQL Fails After 'AT TIME ZONE' Clause

naumadnaumad Posts: 19 Bronze 1
SQL Prompt
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'.

, 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


Sign In or Register to comment.