Search code examples
c#oracle-manageddataaccess

Update DateTime to Oracle DB get not a valid month error from c#

I am making a update to oracle db using Oracle.ManagedDataAccessnuget. I can do update non Date data but when I try to insert data it throws Not a valid month exception.

string cmdQuery = string.Format("UPDATE HKSTF087.FUELCHARGES SET EFFECTIVETHRUDATE = '{0}'  WHERE TITLECODE = '{1}'", currentEffectiveDate.AddSeconds(-1), mtcFuel.TITLECODE);

The string looks like

"UPDATE HKSTF087.FUELCHARGES SET EFFECTIVETHRUDATE = '8/13/2017 11:59:59 PM'  WHERE TITLECODE = 'SCRUBS'"

I tried with other DateTime formats though its not solved.

Solution

  • You can use the Oracle TO_DATE function and format your date in the same way.

    string cmdQuery = string.Format("UPDATE HKSTF087.FUELCHARGES SET EFFECTIVETHRUDATE = TO_DATE('{0:MM/dd/yyyy HH:mm:ss}', 'mm/dd/yyyy hh24:mi:ss')  WHERE TITLECODE = '{1}'", currentEffectiveDate.AddSeconds(-1), mtcFuel.TITLECODE);

    But I would recommend that you use parameters for this.