CODEWITHSUNDEEP
sqlsql-servert-sql
John Stud
John Stud

Reputation: 1779

SQL: How to set a custom MAX datetime?

I tried to convert a non-temporal table into a temporal one. I have two columns, an effective and expiration date.

My expiration dates are set to 2099-12-31 00:00:00 which SQL does not like (I guessing it wants 9999-12-31 etc).

A timestamp this high is unsupported by Pandas in Python, so using such a high date is impractical.

How can I tell SQL to change its max date to something lower / practical?

I get this error:

ADD PERIOD FOR SYSTEM_TIME failed because table contains records where end of period is not equal to MAX datetime.

Upvotes: 3

Views: 2532

Answers (1)

Amirhossein
Amirhossein

Reputation: 1251

I tried to find your query needed.

I explain 3 type of model we can use in temporal tables.

  1. You can use migration and papulation scripts for convert table
ALTER TABLE dbo.AWBuildVersion ADD
[TimeStart] DATETIME2(0) GENERATED ALWAYS AS ROW START NOT NULL,
[TimeEnd] DATETIME2(0) GENERATED ALWAYS AS ROW END NOT NULL CONSTRAINT,
PERIOD FOR SYSTEM_TIME ([TimeStart], [TimeEnd]);
GO
ALTER TABLE dbo.AWBuildVersion
SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.AWBuildVersion_History ) );
GO


ALTER TABLE dbo.[dbo].[Temporal_Table_Demo] SET ( SYSTEM_VERSIONING = OFF );
ALTER TABLE [dbo].[dbo].[Temporal_Table_Demo] ADD ID int IDENTITY (1,1);
ALTER TABLE dbo.[dbo].[Temporal_Table_Demo]
SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.[dbo].[Temporal_Table_Demo_History]));

github link

  1. Create one new and transfer, and if you create historical table with hidden column no need time version in main table like below :
CREATE TABLE dbo.Employee
(
    [EmployeeID]        int NOT NULL PRIMARY KEY CLUSTERED
  , [Name]          nvarchar(100) NOT NULL
  , [Position]      varchar(100) NOT NULL
  , [Department]    varchar(100) NOT NULL
  , [Address]       nvarchar(1024) NOT NULL
  , [AnnualSalary]  decimal (10,2) NOT NULL
  , [ValidFrom]     datetime2 GENERATED ALWAYS AS ROW START
  , [ValidTo]       datetime2 GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
GO

insert into dbo.Employee (
 [EmployeeID]   
,[Name]         
,[Position]     
,[Department]   
,[Address]      
,[AnnualSalary]     
)
Values (3,'Name','Pos','DEp','Add',2)

Update dbo.Employee set Name='AMIR' where EmployeeID=1

now you can see data in main table and history table

--Main table
Select * from dbo.Employee
-- history table
SELECT * FROM Employee
  FOR SYSTEM_TIME
    BETWEEN '2014-01-01 00:00:00.0000000' AND '2021-12-01 00:00:00.0000000'
      WHERE EmployeeID = 1 ORDER BY ValidFrom;

github

  1. For other type you can set time, for adding versioning to non-temporal tables :
CREATE SCHEMA History;
GO

ALTER TABLE InsurancePolicy
    ADD
        SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
            CONSTRAINT DF_SysStart DEFAULT SYSUTCDATETIME()
      , SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
            CONSTRAINT DF_SysEnd DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'), -- or '2099-12-31'
        PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);
GO

ALTER TABLE InsurancePolicy
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.InsurancePolicy));

DocMicrosoft

Upvotes: 1

Related Questions