SQL Server: set NULL value to today's value

问题:

I have a column EntryDate in a SQL Server database.

How can I set a NULL value to fill it with today's date (server time) if value was not provided in a query?

回答1:

Disallow Nulls on the column and set a default on the column of getdate()

/*Deal with any existing NULLs*/
UPDATE YourTable SET EntryDate=GETDATE() WHERE EntryDate IS NULL

/*Disallow NULLs*/
ALTER TABLE YourTable ALTER COLUMN EntryDate DATE NOT NULL

/*Add default constraint*/
ALTER TABLE YourTable ADD CONSTRAINT
    DF_YourTable_EntryDate DEFAULT GETDATE() FOR EntryDate

回答2:

Update table 
set EntryDate = getdate() 
where EntryDate is null

回答3:

Another solution:

Rename your table, and create a view with the same name that does the logic you want, i.e.:

CREATE VIEW TableName
AS
SELECT Column1, Column2, ... ISNULL(EntryDate, GETDATE())
FROM Old_TableName

Then you don't alter your actual data, but if you get a null value for that table it reports today's date.