Why does the FOR Clause not work with an alias in SQL Server 2016 with temporal tables?

Question

I have a SQL Server 2016 database with temporal tables. One temporal table is called Company. I am trying to query it to get current records and all historical records. I first tried the following query:

select *
from Company c
FOR SYSTEM_TIME all

and got the following error: Incorrect syntax near 'FOR'.

However, if I try it without the alias it works fine:

select *
from Company
FOR SYSTEM_TIME all

I could not find any documentation about this- is it a legitimate constraint, a known issue, or something else?

Liddle

did you try it with an AS? Usually SQL Server implies this and it's optional but i'm wondering if it has to be explicit here. – Propertied

Tried with AS, with the same result. – Liddle

Seems to be a limitation - But you can use CTE: ..... WITH _data AS { SELECT * FROM Company FOR SYSTEM_TIME all } SELECT * FROM _data AS d . - see here https://mcmap.net/q/1327268/-c-sqlconnection-querying-temporal-tables – Tighe

Answer 1

This worked for me

select *
from Company FOR SYSTEM_TIME all c

Bladder