I'm running windows 10 and recently updated my MVC app from 32 bit to 64 bit, including the Oracle client.
I installed ODAC1931_x64 on my machine and am using this nuget: https://www.nuget.org/packages/Oracle.ManagedDataAccess/
When I run the app on my desktop, everything works fine. I can pass dates to my DB packages like so:
comm.Parameters.Add(Constants.DATABASE_PARAMETERS.FIELDS.START_DATE, OracleDbType.Date).Value = startDate;
And in the DB package the date come through as:
25-NOV-20
I installed the same ODAC on the server, Windows 2016, and moved my app to it. But when I run it, the date is showing up on the DB as:
20-11-25
And now the DB package is throwing the error:
ORA-01858: a non-numeric character was found where a numeric was expected
This did not happen when I was on 32 bit Oracle, the date came through as 25-NOV-20, it only happens with the 64 bit client and only on the server, not my desktop.
Any idea how to fix this?
You are relying on implicit date conversion in order to display it and enter it as a string. You need to use conversion functions with explicit date formats in order to be safe.
Eg. to_char(my_date_column,'dd/mm/yyyy hh24:mi:ss') if you want to display a date as a string
to_date('25/11/2020 21:00:00','hh24:mi:ss') if you have a string and want to set a date column.
Remember 2 digit years are why every software engineer worked around the clock coming up to the year 2000. Always use 4 digits.