Using YEAR from Prompt entry in Date Selection Criteria

Question

I'm trying to figure out how to use a YEAR entered via a prompt in a date selection criteria... so the user enters 2018 for the Balance_Year... and I want to look for the TAXFORM_DED record with an EFFDT between 01/01/YEAR and 31/12/YEAR. Where YEAR = Prompt value?

Answer 1

using the extract syntax might work.

e.g.

select effdt
from taxform_ded
where extract(year from effdt) between :1 AND :2

Here's a demonstration: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=d33f938fbb1428058cad54f74134bd28

select * from V$VERSION;

CREATE TABLE TAXFORM_DED
    ("EFFDT" timestamp);

INSERT ALL 
    INTO TAXFORM_DED ("EFFDT")
         VALUES ('01-Jan-2010 12:00:00 AM')
    INTO TAXFORM_DED ("EFFDT")
         VALUES ('06-Jun-2013 12:00:00 AM')
    INTO TAXFORM_DED ("EFFDT")
         VALUES ('09-Sep-2019 12:00:00 AM')
SELECT * FROM dual;

select effdt
from taxform_ded
where extract(year from effdt) between 2012 AND 2018;

references:

• https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions050.htm
• https://www.oracletutorial.com/oracle-date-functions/oracle-extract/