Syntax Weirdness with FOR SYSTEM_TIME AS OF [date]

Question

Syntax Weirdness with FOR SYSTEM_TIME AS OF [date]

Sonny Childs

SSC-Addicted

Points: 411
More actions
May 11, 2017 at 9:51 am

#402052

I'm basically trying to get Day-On-Day aggregates of some temporal tables I'm working with.
Basic task #1 is to get counts for each day. I've decided to use a date dimension table for this, hoping that it would make things easier.
Here's what I would assume is the obvious approach:
SELECT dd.[Date], (SELECT COUNT(*) FROM computers FOR SYSTEM_TIME AS OF dd.[Date]) as 'ComputerCount' FROM DateDimension dd
However, SQL Server doesn't care for this code. Throws an "Incorrect syntax near 'dd'." message at me.
Investigating this led me to create a distilled query set of what works and what doesn't with "SYSTEM_TIME AS OF":
SELECT COUNT(*) FROM computers FOR SYSTEM_TIME AS OF '2017-05-06' -- works fine SELECT COUNT(*) FROM computers FOR SYSTEM_TIME AS OF ('2017-05-06') -- syntax error
DECLARE @muhDate datetime = '2017-05-06' SELECT COUNT(*) FROM computers FOR SYSTEM_TIME AS OF @muhDate -- also works fine!!!
Any thoughts? I've previously posted a similar topic which lead to dynamic SQL generation (which actually works), but I was thinking that this would be more sane...except for the fact that SQL doesn't seem to let anything other than a pre-built variable/string into that part of the syntax.
I'm basically trying to step up my day-on-day game without continuing to create dynamic UNION ALL queries for each day.
I can provide more information if necessary.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247159 More actions · Answer 1

I suspect that dd is a reserved word. Try using [dd] instead. I just tried this with no problem, and it's basically the same construct as yours:
SELECT eh.* , Nonsense = ( SELECT COUNT(*) FROM dbo.Emp FOR SYSTEM_TIME AS OF '20150101' ) FROM dbo.EmpHistory eh;

drew.allen SSC Guru Points: 77013 More actions · Answer 2

The values for the system time can only be literals or variables. They cannot be date/time fields.

FROM (Transact SQL)

<system_time> ::= {    AS OF <date_time> | FROM <start_date_time> TO <end_date_time> | BETWEEN <start_date_time> AND <end_date_time> | CONTAINED IN (<start_date_time> , <end_date_time>) | ALL }
<date_time>::=    <date_time_literal> | @date_time_variable
<start_date_time>::=    <date_time_literal> | @date_time_variable
<end_date_time>::=    <date_time_literal> | @date_time_variable

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Sonny Childs SSC-Addicted Points: 411 More actions · Answer 3

Phil, I believe the key here is what you're allowed to put after the "AS OF".
"dd" is not a reserved word, as far as I know, and wrapping it in brackets doesn't change anything.

Of course the query you wrote is going to work; similar to my example, using a hard-coded data after "AS OF" is perfectly fine, but apparently using a SELECT'd date is not valid. I'm hoping there's a workaround for this.

For a test, try doing this:

SELECT
 eh.*
,  Nonsense =
 (
 SELECT COUNT(*)
 FROM dbo.Emp
   FOR SYSTEM_TIME AS OF ('2015' + '0101')
 )
FROM dbo.EmpHistory eh;

or even just:

SELECT
 eh.*
,  Nonsense =
 (
 SELECT COUNT(*)
 FROM dbo.Emp
   FOR SYSTEM_TIME AS OF ('20150101')
 )
FROM dbo.EmpHistory eh;

Maybe this helps to explain what I'm talking about.

It seems you can't pass anything as an expression to the "AS OF" syntax.

Sonny Childs SSC-Addicted Points: 411 More actions · Answer 4

drew.allen - Thursday, May 11, 2017 11:15 AM
The values for the system time can only be literals or variables. They cannot be date/time fields.
FROM (Transact SQL)
<system_time> ::= {    AS OF <date_time> | FROM <start_date_time> TO <end_date_time> | BETWEEN <start_date_time> AND <end_date_time> | CONTAINED IN (<start_date_time> , <end_date_time>) | ALL }
<date_time>::=    <date_time_literal> | @date_time_variable
<start_date_time>::=    <date_time_literal> | @date_time_variable
<end_date_time>::=    <date_time_literal> | @date_time_variable
Drew

Thanks, Drew.

This is the documentation I was looking for to confirm my suspicions. Didn't think to look in the FROM docs.

Any idea how I can work around this requirement outside of my current method of generating hundreds of UNION ALL queries?

Phil Parkin SSC Guru Points: 247159 More actions · Answer 5

Ah, sorry, I should have read your post more closely.
At least it gave me the opportunity to create my first temporal table, so thanks for that!

Razvan Socol SSCarpal Tunnel Points: 4962 More actions · Answer 6

Indeed, the syntax of the FOR SYSTEM_TIME AS OF clause allows only a constant or a variable, not a column.
To overcome this limitation, you can use a table-valued function to return the data from the temporary table at the time of the specified parameter, for example:

CREATE FUNCTION GetComputersCount(@DateTimeUTC datetime2(7)) RETURNS TABLE AS RETURN SELECT COUNT(*) AS ComputerCount FROM computers FOR SYSTEM_TIME AS OF @DateTimeUTC GO SELECT dd.[Date], (SELECT ComputerCount FROM dbo.GetComputersCount(dd.[Date])) as 'ComputerCount' FROM DateDimension dd

andycadley SSCertifiable Points: 5296 More actions · Answer 7

I'd probably use FOR SYSTEM_TIME BETWEEN xxx AND yyy and then JOIN that on your dimension table.

Syntax Weirdness with FOR SYSTEM_TIME AS OF [date]

Cookies on SQLServerCentral