Passing <NULL> to smalldatetime field

[woyler]

Hi,
I am having a bit of a problem with SQL syntax for SQL Server.
I have a field(MY_DATE), of a smalldatetime data type.
When I pass a NULL value to it either from VB or in Query Analyzer, it is assigned '1/1/1900'.
I have tried values of
'<NULL>'
&quot;&quot;
''
CONVERT(DATETIME,'',102)

Am I missing something?
Tahnks in advance,
Bill

 

[MarkSweetland]

Don't know if it fits your case but:

Versions 4.2x, 6.0, 6.5:

Any local variable of fixed length for which there is an implicit or explicit conversion to SMALLDATETIME will inadvertently convert NULL to the default date Jan 1, 1900 12:00AM.

http://support.microsoft.com/support/kb/articles/Q186/8/30.ASP

Mark

 

[ccuster]

Hi I am running slq97 and tried thsi

create table temp1
(my_date smalldatetime null)

insert into temp1
values(null)

select * from temp1

It seems to work, maybe I am misunderstanding something. Let me know.

Chuck

 

[woyler]

The work around I found was:

INSERT INTO TABLE
(STRING_FIELD,DATE_FIELD)
VALUES('1212',1,CONVERT(DATETIME,NULL))

Thanks for your responses

 

[Kmcglon]

Thanks woyler, we ran into this problem before and used a If.. then evaluation to get around it but your solution is better than that, Thanks

km