In a previous employment we used a similar mechanism on date only to force a specific business day into play. We used it in emergency situations and in the testing environment.
One way of achieving something similar to have the override datetime stored in some sort of standing data table. E.g.
create table FixedDate
(
FixedDate DateTime
)
INSERT INTO FixedDate VALUES (cast('2016-01-01 11:15:10' as datetime))
I used a very simple table:
create table DemoDate
(
id int identity(1,1),
DateToRecord DateTime,
SomeOtherData nvarchar(125)
)
Create a trigger on the target table that queries the Fixeddate and uses it if it is available otherwise uses the current date and time.
IF OBJECT_ID('TRG_DemoDate') IS NOT NULL
DROP TRIGGER TRG_DemoDate
GO
CREATE TRIGGER TRG_DemoDate ON dbo.DemoDate
AFTER INSERT AS
BEGIN
DECLARE @fdate DATETIME = (SELECT FixedDate FROM FixedDate)
IF @fdate IS NULL SET @fdate = GETDATE()
UPDATE D SET DateToRecord = @fdate
FROM DemoDate D
INNER JOIN INSERTED I ON D.id=I.id
END
GO
I then ran the following queries (expected results are given in the comments)
INSERT INTO DemoDate (SomeOtherData) VALUES('asdfasdlf')
INSERT INTO DemoDate (SomeOtherData, DateToRecord ) VALUES('11111asdfasdlf', GETDATE())
DELETE FROM FixedDate
INSERT INTO DemoDate (SomeOtherData) VALUES('22222asdfasdlf')
INSERT INTO DemoDate (SomeOtherData, DateToRecord ) VALUES('33333asdfasdlf', CAST('2017-01-01 11:11:11' as datetime) )
SELECT * FROM DemoDate
These were the results:
1 2016-01-01 11:15:10.000 asdfasdlf
2 2016-01-01 11:15:10.000 11111asdfasdlf
3 2017-03-21 12:21:11.033 22222asdfasdlf
4 2017-03-21 12:21:11.043 33333asdfasdlf
Note that in my example any datetime provided during the insert is completely overridden by the rules in the trigger. If you want to retain the provided date when the fixeddate is "switched off" then you will need to also check for a date already on the INSERTED table.
To "switch off" the FixedDate just delete the data from the standing data table
DELETE FROM FixedDate