how to solve that error "Conversion failed when converting date and/or time from character string"

Question

I am creating a new application and I am trying to insert a DateTime data type into SQLServer. the error says

Conversion failed when converting date and/or time from character string.

Code:

string Insertcmd = "INSERT INTO PatientPay (PatientID, Cash, AmountPaid, 
PaymentDate, Reseaon, StaffID) Values (@StaffID, @Cash, @AmountPaid, 
        @type, @PaymentDate, @StaffID)";
        cmd = new SqlCommand(Insertcmd, con);

        SqlParameter[] param = new SqlParameter[6];
        param[0] = new SqlParameter("@PatientID", SqlDbType.Int);
        param[0].Value = PatientID;

        param[1] = new SqlParameter("@Cash", SqlDbType.Float);
        param[1].Value = Cash;

        param[2] = new SqlParameter("@AmountPaid", SqlDbType.Float);
        param[2].Value = AmountPaid;

        param[3] = new SqlParameter("@type", SqlDbType.NVarChar, 255);
        param[3].Value = type;

        param[4] = new SqlParameter("@PaymentDate", SqlDbType.DateTime);
        param[4].Value = Convert.ToDateTime( DateTime.Now.ToString("yyyy- 
        MM-dd 00:00:00"));  // Error

        param[5] = new SqlParameter("@StaffID", SqlDbType.Int, 255);
        param[5].Value = StaffID;
        //Open the connection to database
        con.Open();

        try
        {
            if (con.State == System.Data.ConnectionState.Open)
            {
                //Execute the Adding process
                cmd.Parameters.AddRange(param);
                cmd.ExecuteNonQuery();
             }
         }catch{}

Why are you taking a `DateTime`, converting it to a `string`, and then trying to convert it back to a `DateTime` again? — Rufus L, May 01 '19 at 23:15
Looks like Jed might be trying to strip off the time portion of the DateTime. If so, just use `DateTime.Today` instead. — DBro, May 01 '19 at 23:17

Hadi · Accepted Answer · 2019-05-01T23:45:01.267

Just use

param[4].Value = DateTime.Now;

Instead of

param[4].Value = Convert.ToDateTime( DateTime.Now.ToString("yyyy- 
    MM-dd 00:00:00"));

If you need to remove the time part then use:

param[4].Value = DateTime.Now.Date;

Or as @DBro mentioned in the comments

param[4].Value = DateTime.Today;

Update 1

If you are having problem passing the date parameter try using the following sql command without passing the PaymentDate parameter:

string Insertcmd = "INSERT INTO PatientPay (PatientID, Cash, AmountPaid, 
PaymentDate, Reseaon, StaffID) Values (@StaffID, @Cash, @AmountPaid, 
        @type, DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0), @StaffID)";

The whole code will be:

string Insertcmd = "INSERT INTO PatientPay (PatientID, Cash, AmountPaid, 
PaymentDate, Reseaon, StaffID) Values (@StaffID, @Cash, @AmountPaid, 
        @type, DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0), @StaffID)";
    cmd = new SqlCommand(Insertcmd, con);

    SqlParameter[] param = new SqlParameter[5];
    param[0] = new SqlParameter("@PatientID", SqlDbType.Int);
    param[0].Value = PatientID;

    param[1] = new SqlParameter("@Cash", SqlDbType.Float);
    param[1].Value = Cash;

    param[2] = new SqlParameter("@AmountPaid", SqlDbType.Float);
    param[2].Value = AmountPaid;

    param[3] = new SqlParameter("@type", SqlDbType.NVarChar, 255);
    param[3].Value = type;

    param[4] = new SqlParameter("@StaffID", SqlDbType.Int, 255);
    param[4].Value = StaffID;
    //Open the connection to database
    con.Open();

    try
    {
        if (con.State == System.Data.ConnectionState.Open)
        {
            //Execute the Adding process
            cmd.Parameters.AddRange(param);
            cmd.ExecuteNonQuery();
         }
     }catch{}

Reference

Best approach to remove time part of datetime in SQL Server

Update 2

You must reorder the parameters in the SQL command since you are inserting thr @type parameter in the PaymentDate column.

@DBro i mentioned your comments in the answer. Thanks for the remark — Hadi, May 01 '19 at 23:19
@Jed check the answer update. Also what was the error you received — Hadi, May 01 '19 at 23:29
@Hadi your answer didn't work. this is the error "Conversion failed when converting date and/or time from character string." — Jed, May 01 '19 at 23:39
@Jed You must reorder the parameters in the SQL command since you are inserting thr `@type` parameter in the `PaymentDate` column — Hadi, May 01 '19 at 23:44

how to solve that error "Conversion failed when converting date and/or time from character string"

1 Answers1