Insert datetime2 into database

Question

I am using datetime2 as the datatype for checkIn and checkOut. Previously I can add into database with this code.

//value for checkIn = 12/25/2015 2:00:00 PM
checkIn = DateTime.ParseExact(Session["checkInDate"].ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture).AddHours(14);

//value for checkOut = 12/26/2015 12:00:00 PM
checkOut = DateTime.ParseExact(Session["checkOutDate"].ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture).AddHours(12);

strInsert = "INSERT INTO Reservation ( checkInDate, checkOutDate) VALUES (@checkInDate, @checkOutDate)";

cmdInsert = new SqlCommand(strInsert, conn);
cmdInsert.Parameters.AddWithValue("@checkInDate", checkIn);
cmdInsert.Parameters.AddWithValue("@checkOutDate", checkOut);

But now it doesn't work and I am getting this error;

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

I think the error is caused by the check in value which contain the "PM" and "AM", but it is weird because previously I am able to add this into the database.

Does anybody know how to fix this?

Are you really sure your both column is `datetime2`? In such a case, `AddWithValue` _might_ generate a problem maybe. Have you ever tried it with `Add` and specify your parameter type? — Soner Gönül, Dec 20 '15 at 14:14
Would you post the values from your dates prior to the exception? — Moe, Dec 20 '15 at 14:14
Try w/ `cmdInsert.Parameters.Add("@checkInDate", SqlDbType.DateTime2).value = checkIn;` — Alex K., Dec 20 '15 at 14:21
The error message suggests that `.AddWithValue` is going wrong and converting your datetimes to strings. Which may mean that your `DateTime.ParseExact` has gone wrong. So try giving it a more accurate format to use in parsing: [Why can’t DateTime.ParseExact() parse the AM/PM in “4/4/2010 4:20:00 PM” using “M'/'d'/'yyyy H':'mm':'ss' 'tt”](http://stackoverflow.com/a/2596991/1115360). — Andrew Morton, Dec 20 '15 at 14:22
As a best practice, don't use `AddWithValue` as much as you can. http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ — Soner Gönül, Dec 20 '15 at 14:22

Andrew Morton · Answer 1 · 2015-12-20T15:23:50.890

It appears that you want to discard the time part of checkInDate. Using ParseExact to do that is not really the correct way. Instead you can use the .Date property of a DateTime and then add on the hours.

Also, to avoid the hassles which .AddWithValue can give, simply use .Add, so...

string s = "12/25/2015 2:00:00 PM";
DateTime checkIn = DateTime.Parse(s, CultureInfo.GetCultureInfo("en-US")).Date.AddHours(14);
// ....
string strInsert = "INSERT INTO Reservation (checkInDate, checkOutDate) VALUES (@checkInDate, @checkOutDate)";

using (SqlConnection conn = new SqlConnection(connStr))
{
    using (SqlCommand cmdInsert = new SqlCommand(strInsert, conn))
    {
        cmdInsert.Parameters.Add(new SqlParameter("@checkInDate", SqlDbType.DateTime2).Value = checkIn);
        // ....
    }
}

Note that you can store a DateTime in a Session value, there is no need to store it as a string.

I notice that you quote the checkin date in USA date format (MM/dd/yyyy), but your format in .ParseExact is "dd/MM/yyyy". This may also be a source of trouble.

Insert datetime2 into database

1 Answers1