How to set default null value in to a date field in sql server 2000?

Question

How to set deafult null value in to a date field in the table,if the parameter containing the date value is null?

Answer 1

Do you mean how do you set a specific default value in a stored procedure when the date parameter passed in is NULL?

Mark your date parameter like this:

@DOB DATETIME = '01/01/1900'

And the @DOB parameter would then insert '01/01/1900' if @DOB is passed as NULL.

Answer 2

Try:

coalesce(param, getdate())

in your stored procedure or define a default value on your column

ALTER TABLE dbo.Mitarbeiter ADD CONSTRAINT
DF_Mitarbeiter_Geburtstag DEFAULT getdate() FOR Geburtstag

Answer 3

I am not expert on sql server 2000.

i got one link hope this will help you :)

http://www.c-sharpcorner.com/UploadFile/sd_patel/EnterNullValuesForDateTime11222005015742AM/EnterNullValuesForDateTime.aspx

Answer 4

Make sure you are really passing in NULL, I think you are passing in an empty string. If you pass in empty string, you get the 1900-01-01 00:00:00.000 value that you refer to. Null will be null and not change to a date.

try this test code:

declare @x datetime

select 'is null', @x

set @x=''

select 'is empty string', @x

OUTPUT:

------- -----------------------
is null NULL

(1 row(s) affected)


--------------- -----------------------
is empty string 1900-01-01 00:00:00.000

(1 row(s) affected)