CODEWITHSUNDEEP
oracle-databaseplsql
Neha
Neha

Reputation: 83

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

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.

Upvotes: 0

Views: 304

Answers (1)

MT0
MT0

Reputation: 168361

  • 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;
/

Upvotes: 3

Related Questions