Temporal for FOR SYSTEM_TIME AS OF cannot use datetime functions. - by Eric A. Peterson

Status : 


Sign in
to vote
ID 2582201 Comments
Status Active Workarounds
Type Bug Repros 0
Opened 4/12/2016 3:07:49 PM
Access Restriction Public


Using a datetime function against a temporal table using the "FOR SYSTEM_TIME AS OF" functionality and a function does not work.   


The following is a standard request.   
    SELECT * 
    FROM TemporalTable
    FOR SYSTEM_TIME AS OF '2016-04-12 14:19:27.2087374'
    WHERE PropertyId = 5
--        ******** (1 row(s) affected) ********  <-- Works as designed, returns one row

But if I try to use a function, for example I need to get the value for yesterday.  Normally I would enter a SQL function dateadd(day, -1, getdate()) ,  I get an error as in this example:
    SELECT * 
    FROM TemporalTable
    FOR SYSTEM_TIME AS OF dateadd( day, -1, getdate())
    WHERE Id = 5
--          ******* Msg 102, Level 15, State 1, Line 133   Incorrect syntax near 'dateadd'.  *******

Note I can use a variables but that requires multiple statements of additional work outside of my SQL Statement
    declare @DateExample datetime2
    set @DateExample = dateadd( day, -1, getdate())
    SELECT * 
     FROM TemporalTable
    FOR SYSTEM_TIME AS OF @DateExample
    WHERE Id = 5
--        ******** (1 row(s) affected) ********  <-- returns the row I am looking for, but with extra coding

Sign in to post a comment.