title: Explicitly Set ValidFrom in Temporal Table
body: <p>I'm trying to set the ValidFrom range for the current record in a temporal table.  I'm doing this because I'm rebuilding history from another system (non SQL) into a data warehouse so the current version of records may be "as of" a date that's in the past.  If I can't get this to work, my fall back is to add a row in the history table that fills in the gap but I'm thinking there's a way to get this to work.  Maybe there are some ways with alter columns?</p>
  
  <pre><code>/******** CURRENT TIME=2018-03-10 15:32:26 *****/
  
  CREATE TABLE TestHist(
      ID int NOT NULL,
      Name varchar(max),
      --Temporal Stuff
      ValidFrom datetime2(7) NOT NULL,
      ValidTo datetime2(7) NOT NULL
  )
  GO
  
  CREATE TABLE Test(
      ID int IDENTITY(1,1) NOT NULL,
      Name varchar(max),
      --Temporal Stuff
      ValidFrom datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,
      ValidTo datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,
      PRIMARY KEY CLUSTERED (ID ASC) ,
      PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
  ) 
  WITH( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.TestHist ) )
  GO
  
  ALTER TABLE Test SET (SYSTEM_VERSIONING = OFF)
  go
  
  --THIS WORKS BUT SETS THE VALIDFROM TO CURRENT TIME
  insert into Test(name) values ('fred')
  
  --AND BTW, THIS IS HOW I LOAD THE HISTORY (THIS WORKS TOO)
  insert into TestHist(ID,Name,ValidFrom,ValidTo) values (1,'joe',null,'1/1/18','1/15/18')
  insert into TestHist(ID,Name,ValidFrom,ValidTo) values (1,'steve','fred','2/1/18','3/1/18')
  </code></pre>
  
  <p>But the problem is that it sets the current ValidFrom time arbitrarily to when you do your insert statement:</p>
  
  <pre><code>select * from test
  ID  Name    ParentName  ValidFrom                   ValidTo
  1   fred    NULL        2018-03-10 15:32:26.4403041 9999-12-31 23:59:59.9999999
  </code></pre>
  
  <p>And here's what I wish I could do:</p>
  
  <pre><code>--THIS DOESN'T WORK
  insert into Test(name,ValidFrom,ValidTo) values ('fred','2/1/18','9999-12-31 23:59:59.997')
  </code></pre>
  
  <p>I get this error:</p>
  
  <blockquote>
    <p>Msg 13536, Level 16, State 1, Line 38<br>
    Cannot insert an explicit value into a GENERATED ALWAYS column in table 'CodeAnalytics.dbo.Test'. Use INSERT with a column list to exclude the GENERATED ALWAYS column, or insert a DEFAULT into GENERATED ALWAYS column.</p>
  </blockquote>