6

Since SQL Server 2016 it is possible to automatically create Temporal Tables. I wanted to create a simple query that retrieves the data from a specified date. However, when I try to specify a date in the query like so, it gives a syntax error: 

SELECT * FROM Person FOR SYSTEM_TIME AS OF GETDATE()

I even tried to convert the datatype to a datetime2, since the dates a stored like that, but it still wouldn't work: 

SELECT * FROM Person FOR SYSTEM_TIME AS OF CONVERT(datetime2,GETDATE())

This problem occurrs, but when I first execute SELECT GETDATE() and then copy the text and paste it into the query, it works fine. How can I specify a datetime with the AS OF keyword?

Thanks in advance.

iPhantomGuy
  • 240
  • 1
  • 11

1 Answers1

6

Try this: 

DECLARE @Date DATETIME2 = GETUTCDATE()

SELECT * 
FROM Person
FOR SYSTEM_TIME AS OF @Date

Also, take a look at this article for more examples of querying temporal tables.

Rigerta
  • 3,959
  • 15
  • 26
  • Wow, I cannot for the life of me understand how that works. Now I'm curious. – iPhantomGuy Mar 05 '18 at 14:17
  • Well, it seems it cannot handle a function call on the condition. The same happens if you have a stored procedure and want to call it with `GETDATE()` as param, you will also need this workaround: assign `GETDATE()` to a local variable and pass that as a param instead of the `GETDATE()` function call. – Rigerta Mar 05 '18 at 14:28
  • According to the [docs](https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables), the `SYSTEM_TIME` is determined by the UTC time of the system clock. So might be worth declaring the variable `DECLARE @Date DATETIME = GETUTCDATE()`, as the difference in time zones may make a difference on the query results. – tarheel Mar 05 '18 at 14:50