If you replace @date with the value, it'll work.
But let's look at why it isn't working. Your SQL:
DECLARE @SQL NVARCHAR(1000), @date NVARCHAR(1000);
SET @date = '9999-12-31 00:00:00.000'
SET @SQL = 'ALTER TABLE [spi].[ProductClass]
ADD lastUpdatedTime datetime NOT NULL
CONSTRAINT default_updateTime DEFAULT @date';
EXEC sp_executesql @SQL;
Declares a couple of variables and executes a block of SQL that one of the variables represents. Your issue is that sp_executesql reads some text as SQL and tries to run it - if you can't run what you tell it to run, then it can't.
So if you were to crack open a new query window and attempt to run:
ALTER TABLE [spi].[ProductClass]
ADD lastUpdatedTime datetime NOT NULL
CONSTRAINT default_updateTime DEFAULT @date
It'd have a meltdown trying to figure out what @date is supposed to be.
DECLARE @date NVARCHAR(1000);
SET @date = '9999-12-31 00:00:00.000';
ALTER TABLE [spi].[ProductClass]
ADD lastUpdatedTime datetime NOT NULL
CONSTRAINT default_updateTime DEFAULT @date;
Would work, or simply:
ALTER TABLE [spi].[ProductClass]
ADD lastUpdatedTime datetime NOT NULL
CONSTRAINT default_updateTime DEFAULT '9999-12-31 00:00:00.000';
So, using concatenation you can do:
DECLARE @SQL NVARCHAR(1000), @date NVARCHAR(1000);
SET @date = '9999-12-31 00:00:00.000'
SET @SQL = 'ALTER TABLE [spi].[ProductClass]
ADD lastUpdatedTime datetime NOT NULL
CONSTRAINT default_updateTime DEFAULT ' + @date;
EXEC sp_executesql @SQL;
Which will get you a different error, so try including a pair of quotes in the date variable:
DECLARE @SQL NVARCHAR(1000), @date NVARCHAR(1000);
SET @date = '''9999-12-31 00:00:00.000'''
SET @SQL = 'ALTER TABLE [spi].[ProductClass]
ADD lastUpdatedTime datetime NOT NULL
CONSTRAINT default_updateTime DEFAULT ' + @date;
EXEC sp_executesql @SQL;
sp_executesql's parameters does not works as variable substitution the way you use it. You will need to replace@datewith that string literal when forming the dynamic alter table queryGETDATE()so it matches the insertion time. After that, update columns are updated using triggersdatetimetodatetime2(0)if you only want second precision, or something else if you want better precision.datetimehas many quirks and one of them is that it's only accurate to around 0.003 seconds. The docs strongly warn against using datetime. If you really need milliseconds you'd need to usedatetime2(3)