none
How to handle datetime fields. RRS feed

  • Question

  • I am retrieving a record with a datetime field from an SQL table that contains a null value and displaying the datetime field on a form using a datetime picker. The user has the option to enter a date and save the field back to the SQL table.

    This is part of my get record procedure. If the date is not null I convert it into the field I will be displaying on the form using a datetime picker. If the date is null I set the field to the max date of 12/31/9998 12:00:00 AM.

                        if (!(rdr["mi_date1"] is DBNull))
                        {
                            MiscRec.mi_date1 = Convert.ToDateTime(rdr["mi_date1"]);
                        }
                        else
                        {
                            MiscRec.mi_date1 = xMaxdate;
                        }


    The datetime picker format on the form is set to custom. In the load event of the form I am doing the following...

                        if (miscRec.mi_date1 == dtpmi_date1.MaxDate)
                        {
                            this.dtpmi_date1.CustomFormat = "--/--/----";
                        }
                        else
                        {
                            dtpmi_date1.Text = miscRec.mi_date1.ToShortDateString();
                        }



    The save procedure looks like this.

                    if (dtpmi_date1.Text != "--/--/----")
                    {
                        miscRec.mi_date1 = Convert.ToDateTime(dtpmi_date1.Text);
                    }



    The save is the problem when the user does not change the date, I receive the error message "SQL Datetime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM."
    How do I handle this? Should I change the save procedure to the following?


                    if (dtpmi_date1.Text != "--/--/----")
                    {
                        miscRec.mi_date1 = Convert.ToDateTime(dtpmi_date1.Text);
                    }
                    else
                    {
                        miscRec.mi_date1 = DateTimePicker.MaximumDateTime;
                     }

    I am new to this so any help would be appreciated.

    DrewT1755


    • Edited by DrewT1755 Friday, September 7, 2018 3:09 PM
    Friday, September 7, 2018 3:02 PM

Answers

  • This is really a UI question as we don't know what DateTimePicker control you're using. The issue is that you need to know if the user entered anything. If you're using Winforms then the control has a Value property that gives you back the date. Just use it.

    if (dtpmi_date1.Value != DateTime.MinValue)
       miscRec.mi_date1 = dtpmi_date1.Value;
    else
       miscRec.mi_date1 = DateTimePicker.MaximumDateTime;

    Note that normally we use MinValue to identify a missing value. If you're using MaxValue instead then be aware that when the date gets saved to the DB it'll be the largest date which may not work. More importantly when you read that data back into your UI it probably will render as the max value. Ideally switch to using MinValue. In your DB, if the date is nullable then set it to null if you have a MinValue. If you really want to store MaxValue then update the if check to look for MinValue and MaxValue.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by DrewT1755 Friday, September 7, 2018 6:34 PM
    Friday, September 7, 2018 5:56 PM
    Moderator