The conversion of a varchar data type to a datetime data type resulted in an out-of-range value error

Question

I'm trying to enter data into my table using a form, the date formats in both the form validation and the sql server are both dd/mm/yy, however when i try to submit data from the form with a day higher than 12 (e.g. 13/12/2012) it throws an exception whose cause is "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value error", and if i try to enter data into the form in a mm/dd/yy format it states "wrong date format" which is suppose means that the dd/mm/yy format is the correct format

here's the code for my form below:

    private void btnAddProject_Click(object sender, EventArgs e)
    {
        DateTime startDate;
        DateTime endDate;

        if (txtProjectName.Text == "") //client side validation
        {
            MessageBox.Show("Enter Project Name");
            return;
        }

        try
        {
            startDate = DateTime.Parse(txtProjectStart.Text);
            endDate = DateTime.Parse(txtProjectEnd.Text);
        }
        catch (Exception)
        {
            MessageBox.Show("Wrong Date Format");
            return;
        }
        fa.CreateProject(txtProjectName.Text, startDate, endDate, (int)cbCustomers.SelectedValue, ptsUser.Id);
        txtProjectName.Text = "";
        txtProjectStart.Text = "";
        txtProjectEnd.Text = "";
        cbCustomers.SelectedIndex = 0;
        MessageBox.Show("Project Created");
        adminControl.SelectTab(2);
    }// end btnAddProject

And this is the code in my DAO:

public void CreateProject(string name, DateTime startDate, DateTime endDate, int customerId, int administratorId)
    {
        string sql;
        SqlConnection cn;
        SqlCommand cmd;
        Guid projectId = Guid.NewGuid();

        sql = "INSERT INTO Project (ProjectId, Name, ExpectedStartDate, ExpectedEndDate, CustomerId, AdministratorId)";
        sql += String.Format("VALUES('{0}', '{1}', '{2}', '{3}', {4}, {5})", projectId, name, startDate, endDate, customerId, administratorId);

        cn = new SqlConnection(Properties.Settings.Default.WM75ConnectionString);
        cmd = new SqlCommand(sql, cn);

        try
        {
            cn.Open();
            cmd.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            throw new Exception("Error Creating Project", ex);
        }
        finally
        {
            cn.Close();
        }

    }//end CreateProject Method

This is my code for my facade:

public void CreateProject(string name, DateTime startDate, DateTime endDate, int customerId, int administratorId)
    {
        dao.CreateProject(name, startDate, endDate, customerId, administratorId);
    }//end CreateProject

score 13 · Answer 1 · edited May 23 '17 at 12:02

13

You can do sqldatetime conversion like sqldatetime

 var sqlFormattedDate = myDateTime.Date.ToString("yyyy-MM-dd HH:mm:ss");

edited May 23 '17 at 12:02

Community

1
1

answered Sep 12 '12 at 18:18

Baskaran

273
1
3
13

score 11 · Accepted Answer · answered Sep 12 '12 at 17:51

Basically, you shouldn't be passing the DateTime values as strings in your SQL at all. Use parameterized SQL, and just set the parameter value directly. You should always use parameterized SQL as far as possible:

It gives better code/data separation
It avoids problematic conversions (like this one)
It avoids SQL injection atttacks

Additionally, your exception handling is pointlessly complex. Just use a using statement, and let the SqlException bubble up directly - why bother wrapping it in a vanilla Exception?

score -2 · Answer 3 · edited Sep 12 '12 at 18:39

-2

sql = "INSERT INTO Project (ProjectId, Name, ExpectedStartDate, ExpectedEndDate, CustomerId, AdministratorId)";
sql += String.Format("VALUES('{0}', '{1}', '{2}', '{3}', {4}, {5})", projectId, name, startDate.toString("dd-MMM-yyyy"), endDate.toString("dd-MMM-yyyy"), customerId, administratorId);

A suggestion : why are you using Insert statement in your code? You can have a stored procedure instead.

edited Sep 12 '12 at 18:39

marc_s

732,580
175
1,330
1,459

answered Sep 12 '12 at 17:53

PQubeTechnologies

308
1
3

There's no need to use a stored procedure - or at least, you haven't suggested any benefits that would confer. Just using parameterized SQL should get rid of this problem. – Jon Skeet Sep 12 '12 at 17:59
The solution is convert the datetime to "dd-MMM-yyyy". sql = "INSERT INTO Project (ProjectId, Name, ExpectedStartDate, ExpectedEndDate, CustomerId, AdministratorId)"; sql += String.Format("VALUES('{0}', '{1}', '{2}', '{3}', {4}, {5})", projectId, name, startDate.toString("dd-MMM-yyyy"), endDate.toString("dd-MMM-yyyy"), customerId, administratorId); – PQubeTechnologies Sep 12 '12 at 18:07
That's a *bad* solution. A much *better* solution is to avoid unnecessary string conversions. – Jon Skeet Sep 12 '12 at 20:04
then you can use the CulturInfo. – PQubeTechnologies Sep 13 '12 at 13:45
That's assuming that the database uses the same culture as the system. Why do you want to jump through complicated hoops to create a fragile solution when there's a simple and solution which is cleaner and *works*? Separating parameter values from the SQL is simply good practice - for all types. – Jon Skeet Sep 13 '12 at 13:55
try convert(datetime , startDate , 103) in the insert statement – PQubeTechnologies Sep 15 '12 at 18:33
Why have a conversion in the first place? There's no need. The source value is a `DateTime`, the target value is for a date-related field. Why make things complicated using strings? – Jon Skeet Sep 15 '12 at 18:37

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value error

3 Answers3

Linked