How to assign a Null value to DateTime variable which then goes to sql server?

  • Thread starter Thread starter John Sutter
  • Start date Start date
J

John Sutter

The DateTime type is giving me some grief with null values.

I am using SQLHelper from the data blocks. I have a textbox which accepts a
date. The field is optional. This text field gets converted to a parameter
of type DateTime in a function call.

I am trying to assign System.DateTime.MinValue to that datetime variable if
the user didn't enter a date. However System.DateTime.MinValue gets a value
of "1/1/1" which SQL Server doesn't accept because it's smaller than
1/1/1753.

How can I assign a NULL value to a DateTime which sql server accepts as
real null value as in DBNull.Value. Somehow I am missing a way to convert a
null DateTime to a sql server aware DBNull.Value.

John
 
John said:
The DateTime type is giving me some grief with null values.

I am using SQLHelper from the data blocks. I have a textbox which accepts a
date. The field is optional. This text field gets converted to a parameter
of type DateTime in a function call.

I am trying to assign System.DateTime.MinValue to that datetime variable if
the user didn't enter a date. However System.DateTime.MinValue gets a value
of "1/1/1" which SQL Server doesn't accept because it's smaller than
1/1/1753.

How can I assign a NULL value to a DateTime which sql server accepts as
real null value as in DBNull.Value. Somehow I am missing a way to convert a
null DateTime to a sql server aware DBNull.Value.
Click to expand...

Use the System.Data.SqlTypes.SqlDateTime type.
 
Use the System.Data.SqlTypes.SqlDateTime type.
Click to expand...

How do you convert between System.Data.SqlTypes.SqlDateTime and string or
Datetime? or get a textbox value and put it in SqlDateTime?

John
 
John said:
How do you convert between System.Data.SqlTypes.SqlDateTime and string or
Datetime? or get a textbox value and put it in SqlDateTime?
Click to expand...

Have a look at the docs for SqlDateTime. It's pretty simple:

DateTime dt = DateTime.Now;
SqlDateTime sdt;

// DateTime to SqlDateTime
sdt = new SqlDateTime( dt);

// SqlDateTime to DateTime
if (!sdt.IsNull) {
dt = sdt.Value; // would throw SqlNullValueException if null
}

// string to SqlDateTime
sdt = SqlDateTime.Parse( "3/1/2005"); // or use DateTime's parsing
// for more control
sdt = new SqlDateTime( DateTime.ParseExact( "03/01/2005",
"dd/MM/yyyy",
CultureInfo.InvariantCulture));


// SqlDateTime to string
Console.WriteLine( sdt.ToString());
 
Back
Top