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.

• Phil Parkin

  SSC Guru

  Points: 247180

  More actions

  May 11, 2017 at 10:10 am

  #1942189

  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

  May 11, 2017 at 11:15 am

  #1942197

  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

  May 11, 2017 at 11:17 am

  #1942198

  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

  May 11, 2017 at 11:19 am

  #1942200

  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: 247180

  More actions

  May 11, 2017 at 11:45 am

  #1942206

  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

  September 10, 2018 at 1:05 am

  #2005015

  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

  September 10, 2018 at 4:42 pm

  #2005164

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