passing NULL to date field

[woyler]

Has anyone ecountered the problem of passin g a NULL into a smalldatetime field in SQL Server? It translates it to '1/1/1900'. I have tried all of the following:

'<NULL>'
&quot;&quot;
''
CONVERT(DATETIME,'',102)
vbNullString
Null
vbNull

Anyone?
Thanks, Bill

 

[shepherd]

Try this:
From SQL Server, make sure the date field allows nulls and make sure you have no default value specified for it either. When you make new inserts into the table, the default for the date field should now be null.

 

[woyler]

Both are already set up correctly. I did find the solution though. In my SQL statement, for any date field that is to be assigned a NULL value, this syntax works:

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

Thanks much for your response

 

[shepherd]

I see what was happening -

A better way to do this would be to not pass a parameter for the date field at all - it isn't necessary and you won't have the overhead of an unnecessary convert function (plus it's conmmon practice). Try this instead:

INSERT INTO TABLE
(STRING_FIELD)
VALUES('1212')

 

[TheDrider]

If you did want to pass something, usually needed with dynamically created SQL, you can use NULL explicitly in the SQL statement instead of supplying a value.

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

Drider

 

[woyler]

The way the program is constructed, the INSERT INTO x VALUES(a,b,c) of the SQL stays constant. The values for the fields are collected based on user interactions and populated accordingly. Therefore, it is not an option to change the SQL, and because it is a string variable being passed in it would read 'NULL' rather then the needed NULL