NULL value in datetime type field
I am having a bit of trouble with a date field. I get all the data in a recordset object and wish to condition on the
date field. Specifically, I need to find out if the date is NULL or blank then set another field = 1. If it has a valid date in the field then set this other field to a zero. Trouble is when I check to see what is in that date field, it returns 12/30/1899. But if I step through the code and copy the query that is getting executed to QueryAnalyzer and run it, the field shows NULL for a vaule.
Has anyone ever run into this before? Any suggestions how I can handle it?
Thanks for the help,
Owen Slater replied
The data returned by SQL will contain the null value, regardless of which data retrieval method you are using (eg ADO.NET or DI API recordset object). It is the DI API recordset object that converts this value to the date. You get the same issue if a text field is null in the database but the recordset returns an empty string (rather than null).
Personally, I use the isnull statement to convert nulls in a date field to 01/01/1900 (it's not really very different than testing for 12/30/1899 but if I use the isnull command then I can be sure that if a different patch or version of the DI API returns a different value than 12/30/1899, due to a bug or a design change, then my code will still work).
select isnull(MYDATE, '1900-01-01') as MYDATE from MYTABLE