datetime handling in VB.Net2005 and SQLserver2000

[Winla]

I am seeking a better way to handle DateTimePicker control as an
optional field for users, a scenario is like this:

• In a user interface I have a datetimepicker control for user to select
  a date as an option.
• In Sql server 2000, I have a datetime field to hold its value or set to null
  if it has no value.
• I have two stored procedures to handle saving into database and
  loading into my program with a datetime parameter as either a string
  or datetime.

The way I am using in previous programs is like this:

• In the database, set a datatime field with null as default value.
• In stored procedure, the parameter for the datetime is set as a
  varchar(10) with default value of '', whenever the datetime is '' in the
  save procedure, it must be set a null value for the datetime field in
  the Database table in INsert or Update syntax.
• In .Net program, the value in DateTimePicker must be converted to
  string when calling the stored procedure, If the datatime is not selected,
  its value is "". When loading the data from database, if datetime is null,
  it must be converted into a string "", then when loading ""
  into Datetimepicker , the control is unchecked to show no date was selected.

Do you have a better way to handle this? like using System.Nullable class
or using datetime datatype as parameter in SPs.

Thanks. Your feedback is appreciated very much.

[jmcilhinney]

You're database design is seriously flawed. Don't store dates in varchar columns. Store them in datetime columns. You should NEVER convert binary date objects to strings unless it is absolutely necessary, which normally means only for display purposes. Use datetime columns and your dates will be retrieved into your app as DateTime objects, the very same type as the Value property of the DateTimePicker. When you load data you check whether the filed is null. If it is you uncheck the DTP and if it contains a value you assign that to the Value property of the DTP. When saving you test the Checked property of the DTP and if it's True you assign the Value property to the field and if it's False you assign DBNull.Value to the field.

There is no need to use a text value at any point in that process. System.DateTime objects in your VB code, SqlDbType.DateTime parameters in your SqlCommands and datetime columns in your database.

[Winla]

Thanks jmc. I do use Datetime type in DB. I agree with you that string should not be used in any step of the processing. My code logic was from
vb5/SQL7 long time ago with less flexible controls. That is why I believe I should change it in my new project. Thanks