Using a date variable in a query to fetch records based on a given date in oracle

Question

I need to write a function in oracle plsql that with take a date as an input and return records from a table for that particular day. If no date is given then fetch the records for current day.

Note that the column (purchase_date) is a timestamp(6) type not null column and has an index on it so I would not like to use trunc() function on the column.

Example value present in purchase_date column is --> 01-DEC-21 06.14.06.388855001 AM

create or replace FUNCTION getRecordsForDate(
      input_date         DATE  DEFAULT SYSDATE
    ) RETURN sys_refcursor IS
        data_out     SYS_REFCURSOR; 

    BEGIN    

        OPEN data_out FOR
               SELECT
                    p.product_name,
                    p.product_type,
                    p.purchased_by
                FROM
                    product_details p
                WHERE
                    AND p.purchase_date BETWEEN TO_DATE(input_date, 'DD-MON-YY')
                   -- AND TO_DATE('03-MAR-22 23:59:59', 'DD-MON-YY HH24:MI:SS');  --harcoded value works but I need to use input_date
                   AND 'TO_DATE' ||'(''' || input_date || ' 23:59:59''' ||',' || '''YYYY-MM-DD HH24:MI:SS''' ||')';
                    
        return data_out;        
    END getRecordsForDate;

My concatenation is not working in the last line. It gives me ORA-01858: a non-numeric character was found where a numeric was expected. Not sure what's wrong here. Would someone be able to help.

MT0 · Accepted Answer · 2022-03-04 13:40:31Z

3

Do not use TO_DATE on a DATE.
The last line of the cursor will not work as it is a (concatenated) string literal that cannot be converted to a TIMESTAMP or a DATE.
Even if it did work (which it will not), your purchase_date is a TIMESTAMP(6) data type so you are going to exclude all value from the time 23:59:59.0000001 until 23:59:59.999999.

You want to use:

create or replace FUNCTION getRecordsForDate(
  input_date DATE  DEFAULT SYSDATE
) RETURN sys_refcursor
IS
  data_out SYS_REFCURSOR; 
BEGIN    
  OPEN data_out FOR
    SELECT product_name,
           product_type,
           purchased_by
    FROM   product_details
    WHERE  purchase_date >= TRUNC(input_date)
    AND    purchase_date <  TRUNC(input_date) + INTERVAL '1' DAY;

  return data_out;        
END getRecordsForDate;
/

edited Mar 4, 2022 at 13:40

answered Mar 4, 2022 at 13:33

MT0

173k12 gold badges70 silver badges136 bronze badges

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

Collectives™ on Stack Overflow

Using a date variable in a query to fetch records based on a given date in oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related