How to Handle [Null] Values of Datetime from SQL Server to a Datetimepicker

Setting DateTimePicker to Null

Use the following code:

dateTimePicker1.Format = DateTimePickerFormat.Custom;
dateTimePicker1.CustomFormat = " ";

How to alter a .NET DateTimePicker control to allow enter null values?

Here's an approach from this CodeProject article on creating a Nullable DateTimePicker.

I have overridden the Value property to accept Null value as DateTime.MinValue, while maintaining the validation of MinValue and MaxValue of the standard control.

Here's a version of the custom class component from the article

public class NullableDateTimePicker : System.Windows.Forms.DateTimePicker
{
    private DateTimePickerFormat originalFormat = DateTimePickerFormat.Short;
    private string originalCustomFormat;
    private bool isNull;

    public new DateTime Value
    {
        get => isNull ? DateTime.MinValue : base.Value;
        set
        {
            // incoming value is set to min date
            if (value == DateTime.MinValue)
            {
                // if set to min and not previously null, preserve original formatting
                if (!isNull)
                {
                    originalFormat = this.Format;
                    originalCustomFormat = this.CustomFormat;
                    isNull = true;
                }

                this.Format = DateTimePickerFormat.Custom;
                this.CustomFormat = " ";
            }
            else // incoming value is real date
            {
                // if set to real date and previously null, restore original formatting
                if (isNull)
                {
                    this.Format = originalFormat;
                    this.CustomFormat = originalCustomFormat;
                    isNull = false;
                }

                base.Value = value;
            }
        }
    }

    protected override void OnCloseUp(EventArgs eventargs)
    {
        // on keyboard close, restore format
        if (Control.MouseButtons == MouseButtons.None)
        {
            if (isNull)
            {
                this.Format = originalFormat;
                this.CustomFormat = originalCustomFormat;
                isNull = false;
            }
        }
        base.OnCloseUp(eventargs);
    }

    protected override void OnKeyDown(KeyEventArgs e)
    {
        base.OnKeyDown(e);

        // on delete key press, set to min value (null)
        if (e.KeyCode == Keys.Delete)
        {
            this.Value = DateTime.MinValue;
        }
    }
}

sending null values from datetimepicker to the database

It doesn't look like parameter purchdate is used anywhere.

Maybe try something like this:

   //cmd.Parameters.Add("@purchasedate", SqlDbType.DateTime).Value = Date.Text;
   if (Date.Checked)
        cmd.Parameters.AddWithValue("@purchasedate", Date);
   else
        cmd.Parameters.AddWithValue("@purchasedate", DBNull.Value);

How to extract date time from sql and bind it to datetimePicker

The problem is the type of the field Birthday.

You say you have it as a NVARCHAR column. This is wrong for many reasons.

First the database is not able to do appropriate searches on that field. For example: is "28-12-2000" before "14-01-2001"? If you look at it as a string NO because 1 is before 2, but if you look at it as a Date then YES.

Then you will have troubles converting that value back to a datetime variable when loading it. The conversion will assume the CultureInfo of the machine where the conversion occurs. This could work correctly or not depending on the input. For example on my machine the string "04-06-2017" will be converted in the 4th of June, but on a machine where the culture info is set to MM-dd-yyyy the same string will be parsed to the 6th of April.

So you just need to change that field to a datetime type, store values as DateTime variables and read them back with

birthday_search.Value = Convert.ToDateTime(reader["Birthday"]);

or

birthday_search.Value = (DateTime)reader["Birthday"];

No formatting involved here. A field of type datetime is assigned to a property of type DateTime. The formatting to display this value is the job of the Format and CustomFormat of the DateTimePicker. Of course this code doesn't consider the possibility of a NULL value.

How to get saved date value from Sql Server in dateTimePicker Control in Windows Application

Ankush,
I noticed you're trying to set dateTimePickerJoinDate in two places in different ways. The first way looks like you're trying to convert the string "JoinDate" to a date, which should throw an error.
Have you tried this syntax:

dateTimePickerJoinDate.Value = dt.Rows[0]["JoinDate"].ToString();
-- or
dateTimePickerJoinDate.Value = Convert.ToDateTime(dt.Rows[0]["JoinDate"]);

--Jim

---

Related Topics