DATE is updated as 1900-01-01 00:00:00

[yajmere]

Hi,
I have a strange problem. When I try to update record in a smalldate field it updates the date as 1900-01-01 00:00:00.

I use SQL Query

update regions set processstart = '' where ntid = 'yajmi'

if I change this query to - update regions set processstart = NULL where ntid = 'yajmi', then it inserts NULL value.

But because of certain conditions in my program I can not use NULL in place of ''.


Does anyone has solution for this?

Thanks
Yogesh

 

[SQLSister]

Date fields don't accept empty string as values, so by default it will fill in the date you showed. Since you are unlikely to have any real dates like that just use that in your code where you were going to use the empty string. Or adjust your user interface to accept null avalues and put null in the field.

 

[tlbroadbent]

The empty string '' is converted to zero when stored in a SQL datetime field. The zero date is Jan 1, 1900. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.