how can save DateTime c# language to oracle10g dat

Question

there is a table== create table newstudent(enroll int, name varchar2(20), DoJoin date); in c# when i type

oraclecommand(1,'amit purohit', DateTime.Now());
//Error found 'nvalid month'
oraclecommand(1,'amit purohit', String.Format("{0:dd-MMM-yyyy}"));
//problme that save only date into database but i need time also. like '11-AUG-2009 11:22:32.0'
oraclecommand(1,'amit purohit', String.Format("{0:dd-MMM-yyyy hh:mm:ss}")
//also error get like string picture format in date time values

Answer 1

One of your problems may be the fact that your date is in the wrong format for Oracle.

Oracle accepts the yyyy/mm/dd date format which I can see you're not using.

I'm with Jon Skeet on this one for the oraclecommand but you maybe want to try creating a normal SQL Query inside a string and then running the query.

You can use the to_date function to convert your date into the correct format.

insert into table_name
(date_field)
values
(to_date('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'));

(Via Standard Date and Time Format Strings)

Or on the other hand you may try converting the date to a proper Oracle accepted format using some C# functions: Oracle/PLSQL: Insert a date/time value into an Oracle table

Answer 2

Did you check standard date format

Answer 3

Try something like this (non-tested code):

public void InsertDateTime(DateTime aoDateTime) {
  using (var oracleConnection = new OracleConnection("<my connection string>")) 
  {
    var oracleCommand = new OracleCommand(
      "INSERT INTO MY_TABLE (MY_DATE_FIELD) VALUES (:pMY_DATE_VALUE)", 
      oracleConnection);
    oracleCommand.Parameters.Add(
      new OracleParameter(":pMY_DATE_VALUE", aoDateTime));

    oracleConnection.Open();
    oracleCommand.ExecuteNonQuery();
  }
}

Answer 4

You should use a parameterized statement, and add a parameter with the relevant data in, specifying the appropriate parameter type. The exact format of that will depend on which Oracle driver you're using, but it's likely to be something like:

command.Parameters.Add(":date", OracleDbType.DateTime).Value = ...;