Windows Server 2016 Passing Dates Differently to Oracle 64 bit Client

I have an MVC application hosted on Windows Server 2016 that was using the 32 bit Oracle client to connect to a DB. I upgraded it to the 64 bit client.

The problem is that the server is now passing the dates in a different format to the DB. It passes it using C#:

comm.Parameters.Add(DATABASE_PARAMETERS.FIELDS.START_DATE, OracleDbType.Date).Value = startDate; //startDate is a DateTime variable

When that date hit the DB, it was coming in the format 'dd-MMM-yyyy'. Since switching to the 64 bit client, the date is showing up on the DB side as 'yy-mm-dd'.

So the format the server is passing the date as has changed, which causing errors on the DB. The DB contains hundreds of packages, so changing the DB code to reformat all the dates is not an option, I need to figure out how to get the server to pass them correctly again. It still works fine from my desktop using the exact same code, this is only a problem once deployed to the server.

I have tried changing the short date format in the region setting in the control panel. I have tried changing the short date value in the registry "HKEY_USERS -> .DEFAULT -> Control Panel -> International".

I have tried passing the date a formatted string instead of DateTime.

I have tried adding this code in the Global.asax.cs of the web project:

        protected void Application_BeginRequest(Object sender, EventArgs e)
        {
            System.Globalization.CultureInfo newCulture = (System.Globalization.CultureInfo)System.Threading.Thread.CurrentThread.CurrentCulture.Clone();
            newCulture.DateTimeFormat.ShortDatePattern = "dd-MMM-yyyy";
            newCulture.DateTimeFormat.DateSeparator = "-";
            System.Threading.Thread.CurrentThread.CurrentCulture = newCulture;
        }

Nothing is working. The date is still showing up on the DB as 'yy-mm-dd'.

Any idea how I can fix this?

Edit: To add more to this, the error that is specifically being thrown is:

ORA-01858: a non-numeric character was found where a numeric was expected

And is happening on a line in the DB:

l_end_date := '1-jan-2099';

So adding a format to this:

l_end_date := to_date('1jan2099', 'dd-mon-yyyy');

Solves that problem, but I cannot do this to every date variable across the entire system.

The question is why this executes no problem when I run it from my desktop, but causes an error when I run it from the server when they are running the exact same code and the same Oracle client? And why only once I switched to 64 bit Oracle?

I was saying the server is passing it differently because in the DB package, the first thing I try as a debugging step is to print the value of the In parameter.

When I print the variable as it's passed from my desktop, it's in the format 'dd-MMM-yyyy'. When I use the 32 bit client, it's the same. But when deployed to the server it's 'yy-mm-dd'.

So I think it's something on the server, because it works on my desktop, and with the 32 bit Oracle client.

Edit 2, here's some more code that's been asked for. Here's the C# function that calls the DB package:

        public CustomObject GetList(string username, DateTime? startDate, DateTime? endDate)
        {
            conn.Open();
            using (comm = new OracleCommand("db.funtion", conn))
            {
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.Add(TASConstants.DATABASE_PARAMETERS.FIELDS.BATCH_OUTPUT_CURSOR, OracleDbType.RefCursor, ParameterDirection.Output);
                comm.Parameters.Add(TASConstants.DATABASE_PARAMETERS.FIELDS.USER_NAME, OracleDbType.Varchar2, TASConstants.DATABASE_PARAMETERS.LENGTHS.USER_NAME).Value = username;
                comm.Parameters.Add(TASConstants.DATABASE_PARAMETERS.FIELDS.START_DATE, OracleDbType.Date).Value = startDate;
                comm.Parameters.Add(TASConstants.DATABASE_PARAMETERS.FIELDS.END_DATE, OracleDbType.Date).Value = endDate;
                comm.Parameters.Add(TASConstants.DATABASE_PARAMETERS.FIELDS.MESSAGE_FLAG, OracleDbType.Varchar2, TASConstants.DATABASE_PARAMETERS.LENGTHS.MESSAGE_FLAG);
                comm.Parameters[TASConstants.DATABASE_PARAMETERS.FIELDS.MESSAGE_FLAG].Direction = ParameterDirection.Output;
                comm.Parameters.Add(TASConstants.DATABASE_PARAMETERS.FIELDS.MESSAGE_BODY, OracleDbType.Varchar2, TASConstants.DATABASE_PARAMETERS.LENGTHS.MESSAGE_BODY);
                comm.Parameters[TASConstants.DATABASE_PARAMETERS.FIELDS.MESSAGE_BODY].Direction = ParameterDirection.Output;

                OracleDataAdapter da = new OracleDataAdapter(comm);
                DataTable result = new DataTable();
                BatchList batchList = new BatchList();

                try
                {
                    da.Fill(result); //error throw here

The DB package

  Procedure function(p_ssp_rec_refcur  Out ssp_rec_refcur
                            ,p_username        In Varchar2
                            ,p_start_date      In Date
                            ,p_end_date        In Date
                            ,p_successful_flag Out Varchar2
                            ,p_message         Out Varchar2) As
    
    l_start_date Date := p_start_date;
    l_end_date   Date := p_end_date;
  Begin
    If Not ws_base.authenticate_fn(p_username, 8013, ws_base.c_update, p_message) Then
      Return;
    End If;If p_end_date Is Null Then
      l_end_date := '1-jan-2099'; --error thrown here
    End If;
  
    If p_start_date Is Null Then
      l_start_date := '1-jan-1901'; 
    End If;

So yes, adding a date format to the line for l_end_date solves the issue, but management will not accept that solution because 1) it worked with the 32 bit Oracle client, and 2) it works with the 64 bit client from our desktops. Same code, same client installed.

The mystery here is why that is the case?