Date Defaulting to 1/1/1900

[mdr2273]

I have a table with a datetime field that when I update it with a '' value instead of a null in a SQL statement the date is set to 1/1/1900. There are no defaults or triggers set on this field/table. How can I get around this?

 

[gmmastros]

Well... a DateTime field can be NULL or a valid date. Jan 1, 1900 is the 0 date. You can convert 'empty' dates to null like this...

[tt][blue]
Update Table
Set DateField = NullIf([!]SomeDate[/!], '')
Where ....
[/blue][/tt]

In this case, SomeDate would be a date value, or an empty string. NullIf will return the first parameter if it is not equal to the second parameter. If the first parameter is the same as the second parameter, then NULL is returned from NullIf.

Ex:

Select NullIf('1/1/2007', '')
Select NullIf('', '')

Run both of these in Query Analyzer for a better understanding of NullIf.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbenson001]

You can't. A datetime column is just that, it is a date and a time. If you want to store blank values then you will have to use a char or varchar column type.

 

[SQLBill]

Check to make sure that the column allows NULLs.

-SQLBill

Posting advice: FAQ481-4875