Insert datetime to oracle database using c#

Question

I want to insert date into an Oracle database but getting error with parameterized structure

using (OracleCommand command = new OracleCommand("insert into sometable (startup_time) values (:startup_time)", oraconrpsdb))
{
    command.Parameters.Add("startup_time", Convert.ToDateTime(dtDBInfo.Rows[0]["STARTUP_TIME"]));
    command.ExecuteNonQuery();
}

Value is not empty and it has correct structure but I get this error:

String was not recognized as a valid DateTime

When I debug the value of STARTUP_TIME is : 09:23:07 21/09/19

Where does `dtDBInfo` come from? What type is column "STARTUP_TIME" in this table? — SQL Police, Sep 21 '19 at 11:14
please see this link: https://stackoverflow.com/a/2193037/5576498 — Amin Golmahalleh, Sep 21 '19 at 11:35

score 1 · Answer 1 · answered Sep 21 '19 at 12:04

The Convert.DateTime function doesn't test for every possibility of string input.. it really couldn't since some inputs are just ambiguous. Like, does "21/09/19" represent Sept 19, 2021 or Sept 21, 2019?

You can tell it exactly what format your string is in using DateTime.ParseExact:

DateTime.ParseExact("09:23:07 21/09/19", "hh:mm:ss dd/MM/yy", null)

mason · Answer 2 · 2019-09-21T16:13:55.513

Your first problem is getting values out of dtDBInfo. You didn't create a minimal reproducible example but I'm going to assume it's a DataTable based on the name.

The object contained in that column is already a DateTime (unless youre storing dates as strings, which is a bad idea). There's no need to convert it. You can use generics to actually treat it as a DateTime. Change your code to this:

command.Parameters.Add("startup_time", dtDBInfo.Rows[0].Field<DateTime>("STARTUP_TIME"));

Your next problem is going to be that your arguments to your Add method are incorrect. Add doesn't take the object value in. It takes info about the type of parameter, and returns you an OracleParameter that you can then set the value of. Do this:

command.Parameters.Add("startup_time", OracleType.DateTime).Value = dtDBInfo.Rows[0].Field<DateTime>("STARTUP_TIME");

You may be able to get away with eliminating specifying the type of the DateTime altogether. I don't have an Oracle database to test with, but you can try this:

command.Parameters.Add("startup_time", OracleType.DateTime).Value = dtDBInfo.Rows[0]["STARTUP_TIME"];

Insert datetime to oracle database using c#

2 Answers2