Format SQL mistaking 'at time zone' as column alias

naumad

Format SQL is mistaking the 'at time zone' clause as a column alias and attempts to add 'AS' before 'at time zone'.

James R

Hi naumad,

Thanks for your feedback.
We could do with a bit more information to reproduce this bug, could you post an example script where the issue occurs?

Thanks,
James

naumad

I have a few queries where I convert a timestamp to utc and then to a local timezone (from a joined table) as such:

convert(date, x.timestamp at time zone 'UTC' at time zone tz_name) as timestamp_local

SQL Prompt will sometimes try to add 'AS' before each 'at' in the statement.

James R

Hi naumad,

Unfortunately I still can't reproduce this locally. Could you tell me a few more things?

1) What version of SQL Prompt are you using?

2) What option do you have set for SQL Prompt > Options > Actions > Apply column alias style?

3) Does this issue still occur if you just use the inbuilt timezones (e.g 'UTC') and not the local timezone from the joined table?

Kind regards,
James

naumad

1) What version of SQL Prompt are you using?
SQL Prompt 7.5.0.675

2) What option do you have set for SQL Prompt > Options > Actions > Apply column alias style?
column AS alias

3) Does this issue still occur if you just use the inbuilt timezones (e.g 'UTC') and not the local timezone from the joined table?

If I use a literal value, this problem does not occur.

Michael Clark

Just a quick note -- we are tracking this alias bug as SP-6200

Michael Clark

Hi naumad,

Like James, I'm having trouble reproducing this one. Would it be possible to have a larger script where the problem occurs?

Would it be possible to try our new formatting engine and let us know if the problem still occurs? This is currently an experimental feature which you can turn on by going to "SQL Prompt > Options > Labs > Experimental Features" and selecting the "Use new formatting styles" checkbox. That will help us narrow down the cause.

Thanks,

Michael Clark
Technical Lead, SQL Prompt