Setting the end date as 31/12/yyyy-CodePudding

I am working on reports wherein if I am running the report for the year 2021 and if my activity continues till 2022 then the end date of the activity should be set as 31/12/2021. How can I achieve this.

CodePudding user response：

If you pass the year as a parameter, then such a query might return what you want:

SQL> select add_months(trunc(to_date(&par_year, 'yyyy'), 'yyyy'), 12) - 1 result from dual;
Enter value for par_year: 2021

RESULT
----------
31/12/2021

SQL>

to_date converts e.g. 2021 to a valid date datatype value (it will be set to the 1st of current month of that year; at the moment, 01/06/2021)
trunc resets it to 1st of current year (01/01/2021)
add_months adds 12 months (which is then 01/01/2022) so you have to subtract 1 day from it to set the final result to 31/12/2021

CodePudding user response：

Use LEAST:

SELECT LEAST(activity_end, TO_DATE(:your_year || '1231', 'YYYYMMDD')) AS end_date
FROM   your_table