datetime - 1/1/1900 treated as blank??

• d short

  Say Hey Kid

  Points: 687

  More actions

  April 17, 2009 at 12:49 pm

  #208387

  I knew that a datetime field defaulted to '1/1/1900' when the field contains blanks, but it's looking like the 'inverse' is also true, which is causing problems.

  that is, when I specifically insert a value of 1/1/1900 into a datetime (or smalldatetime) field, it's treated as blank. I figure I must be missing something, but consider the following:

  create table testdate2 (datefield smalldatetime,thekey int identity(1,1),descr varchar(20))

  insert testdate2 (datefield,descr) values('','blank')

  insert testdate2 (datefield,descr) values('1/1/1900','1/1/1900')

  insert testdate2 ( descr) values('null')

  select * from testdate2

  select * from testdate2 where datefield is null -- OK

  select * from testdate2 where datefield ='1/1/1900' -- OK

  select * from testdate2 where datefield ='' -- no, one of them really should have '1/1/1900'

  select * from testdate2 where datefield ='' and datefield ='1/1/1900' -- ?

  Like I said, I must be missing something because I can't find this problem addressed anywhere.

  tia.....