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
– SQL Police, Commented Sep 21, 2019 at 11:14

Grant Winney · Accepted Answer · 2019-09-21 12:04:18Z

1

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)

answered Sep 21, 2019 at 12:04

Grant Winney

66.6k13 gold badges120 silver badges167 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mason · Accepted Answer · 2019-09-21 16:13:55Z

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"];

Collectives™ on Stack Overflow

Insert datetime to oracle database using c#

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related