ali raza

C#, .Net ,Asp.net and my thoughts

how to insert dateTime in Oracle database using c#

hi all, i have facing problem while Executing Insert query with dataTime on Oracle database. This query is generated by string manuplation using C# at business layer . i fix the date time as in Query

"TO_DATE('" + ProdMonitor.TRANSACTIONDATE.Month+
"/" + ProdMonitor.TRANSACTIONDATE.Day +
"/" + ProdMonitor.TRANSACTIONDATE.Year+
" " + ProdMonitor.TRANSACTIONDATE.ToLongTimeString()+
"','mm/dd/yyyy HH:MI:SS PM'),"

The Whole Query Will be like

 

Insert Into JOBS (JOB_ID , EXECUTION_TIME,CREATION_TIME,SERIAL_NO,STATUS) values(JOB_ID_SEQ.NEXTVAL,TO_DATE('9/29/2008 11:00:00 PM','mm/dd/yyyy HH:MI:SS PM'),TO_DATE('9/26/2008 12:59:59 PM','mm/dd/yyyy HH:MI:SS PM'),'','1')

Chears

2 responses to “how to insert dateTime in Oracle database using c#

  1. Ingle Ravi Ramkrishna January 9, 2009 at 5:49 am

    In Oracle Date format is

    dd (- | /) month’s name’s first three letters (- | /) yyyy
    i.e. 01-JAN-2008 or 01/JAN/2009

    so what I did is I wrote a function which will convert C# date format to Oracle date format & now it’s accepting the date

    First convert the available date to String and then pass it to function To_Oracle_Date().
    private String To_Oracle_Date(String Given_Date)
    {
    DateTime dt = Convert.ToDateTime(Given_Date);
    String month=””;
    switch (dt.Month)
    {
    case 1: month = “JAN”;
    break;
    case 2: month = “FEB”;
    break;
    case 3: month = “MAR”;
    break;
    case 4: month = “APR”;
    break;
    case 5: month = “MAY”;
    break;
    case 6: month = “JUN”;
    break;
    case 7: month = “JUL”;
    break;
    case 8: month = “AUG”;
    break;
    case 9: month = “SEP”;
    break;
    case 10: month = “OCT”;
    break;
    case 11: month = “NOV”;
    break;
    case 12: month = “DEC”;
    break;
    default: MessageBox.Show(“INVALID MONTH”);
    break;
    }
    return ((dt.Day.ToString() + “-” + month + “-” + dt.Year.ToString()).ToString());

    }

  2. Anthony Smith July 20, 2009 at 11:23 pm

    use ToString with a dateformat. It is easier and quicker

    dt.ToString(“YYYY-MM-DD”) ;

    etc.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: