0

I am trying to retrieve data from oracle database between 2 dates. I keep getting the following error:

An exception of type 'Oracle.ManagedDataAccess.Client.OracleException' occurred in Oracle.ManagedDataAccess.dll but was not handled in user code

I'm not sure if the problem from the query I wrote:

public List<GetterAndSetterObj> GetDataBasedOnSelectedPeriod(DateTime fromDate, DateTime toDate)
    {

        using (ProdOracleContext oracleContext = new ProdOracleContext())
        {
            using (OracleCommand command = oracleContext.CreateCommand)
            {
                command.CommandText = string.Format("select * from datesTable where TRUNC(dates) BETWEEN TO_DATE('{0}','DD-MON-RRRR') AND TO_DATE('{1}','DD-MON-RRRR')", fromDate, toDate);
                var queryResult = oracleContext.Get(command);
                var list_v_sum_slaies_by_lice_no = new List<getterAndSetterObj>();
                foreach (DataRow row in queryResult.Rows)
                {
                    finalList.Add(new getterAndSetterObj
                    {
                        LICENCE_NUMBER = row["LICENCE_NUMBER"].ToString(),
                        SHOP_NAME = row["SHOP_NAME"].ToString(),
                        dates= row.Field<DateTime>("dates"),
                        SUM_ALL = int.Parse(row["SUM_ALL"].ToString())
                    });
                }   return finalList;
            }
        }
    }

Then I call the function using these method:

public List<v_sum_slaies_by_lice_no> GetDataBasedOnSelectedPeriod(string fromDate, string toDate)
    {
        DateTime OfromDate = DateTime.ParseExact(fromDate, "dd/MM/yyyy", null);
        DateTime OtoDate = DateTime.ParseExact(toDate, "dd/MM/yyyy", null);  
        return dataBase.GetDataBasedOnSelectedPeriod(OfromDate, OtoDate);
    }

note, the input would be something like this: 20/06/2019 22/06/2019

Note, the date stored on the database as a datetime which include the time and I want to get the data based on the date only

AbdallahRizk
  • 818
  • 1
  • 8
  • 18
  • Please show the whole stack trace, as the error you show is not very useful. – OldProgrammer Jun 25 '19 at 23:47
  • this is the only output I get no addition information. note, I think the issue got to be with query I believe what I wrote is not readable to oracle database. @OldProgrammer – AbdallahRizk Jun 26 '19 at 00:09
  • take a look here - https://stackoverflow.com/questions/23214898/how-to-pass-datetime-parameter-in-sql-query-for-oracle-db – OldProgrammer Jun 26 '19 at 00:18

1 Answers1

0

Try to change the below line of code. If it will not work share the actual oracle error.

 command.CommandText = string.Format("select * from datesTable where TO_CHAR(dates, 'DDMMYYYY') BETWEEN '{0}' AND '{1}'", fromDate.ToString("ddMMyyyy"), toDate.ToString("ddMMyyyy"));
D.J.
  • 376
  • 4
  • 16