Null Dates are not 1/1/1900

  • Thread starter Thread starter David Lozzi
  • Start date Start date
D

David Lozzi

So, I am sending my sql proc a value of '' or null for a date field. When
the record saves, the field has 1/1/1900, not a null. How can I fix this?

Thanks!!

David Lozzi
 
David Lozzi delphi-ts.com> said:
So, I am sending my sql proc a value of '' or null for a date field. When
the record saves, the field has 1/1/1900, not a null. How can I fix this?
Click to expand...

Check the db schema - does the date column have a default value?
 
You have to specifically send the value DBNull.Value if you want the
underlying column in your db to store the <NULL> value:

This works (assumes startDate is a string that may hold a valid date or be
zero-length):

object startDateToGo; //defined as object because 'object' can hold the
value - DBNull.Value
if(startDate.Length == 0){
startDateToGo = DBNull.Value;
}
else {
startDateToGo = Convert.ToDateTime(startDate);
}
SqlParameter pStartDate = new SqlParameter("@StartDate",
SqlDbType.DateTime);
pStartDate.Value = startDateToGo;
cmd.Parameters.Add(pStartDate);


-HTH

-S
 
select PeriodEndDate, convert(varchar, isnull(PeriodEndDate, ''), 101) as
ConvertDate, cast(isnull(PeriodEndDate, '') as varchar) as CastDate
from TableName

PeriodEndDate ConvertDate CastDate
------------------ -------------------------- ------------------------------
NULL 01/01/1900 Jan 1 1900 12:00AM

Are you using isnull() instead of just SELECTing the date column by itself.
 
DateTime values are stored as numbers. A SQL Server DateTime value can
represent a date between January 1, 1900, through June 6, 2079. Whe you put
a DateTime into a column, you put it in as a string. Putting a blank string
in apparently is interpreted as 0, which would represent January 1, 1900. A
blank string is not a null value. Put NULL into the field instead, and make
sure that the column accepts NULL values.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Sometimes you eat the elephant.
Sometimes the elephant eats you.
 
Back
Top