Is it possible to substitute a value for null in a sql server select statement?

Question

I am moving data from one table to another and I want a date field to change from null to a default value. What is the best way to do this in SQL Server(2000) ?

I want something similar to the IIf function in Access. Like IIF(DateBegin is null, #1/1/2000#,DateBegin)

Answer 1

Use the ISNULL operator.

Answer 2

You can use coalesce to do this.

Answer 3

INSERT INTO destTable (dateColumn, otherColumn)
SELECT ISNULL(dateColumn, '2000-01-01'), otherColumn
FROM sourceTable

Answer 4

You could use the COALESCE operator in your select statement:

SELECT ..., COALESCE(datecolumn, 'default date') FROM SOURCE_TABLE

It returns the first non-null value.