How to retrieve data from oracle database based between 2 datetime field knowing input only date?

Question

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

Please show the whole stack trace, as the error you show is not very useful. — OldProgrammer
– OldProgrammer, Commented Jun 25, 2019 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
– AbdallahRizk, Commented Jun 26, 2019 at 0:09

D.J. · Accepted Answer · 2019-06-26 12:37:27Z

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"));

edited Jun 26, 2019 at 12:37

answered Jun 26, 2019 at 12:02

D.J.

3864 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to retrieve data from oracle database based between 2 datetime field knowing input only date?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related