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?

Please provide enough code so others can better understand or reproduce the problem. — Community, Mar 09 '22 at 09:55

qyb2zm302 · Answer 1 · 2022-11-10T17:46:55.120

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:

Using YEAR from Prompt entry in Date Selection Criteria

1 Answers1