how can save DateTime c# language to oracle10g database

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

Well we've no idea what "oraclecommand" is. What you've posted isn't valid C# anyway, as 'amit purohit' isn't a valid string (wrong quotes). Please post actual code. Ideally, post a short but complete program (e.g. a console app) which demonstrates the problem.

You should use a parameterised 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.

Please provide more information.

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

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

Thanks