Select the first day and last day of this year in DB2

user_10030913 0
Viewed 2165

How to get the first day and the last day for the current year in DB2 in format YYYYMMDD?

I tried:

SELECT SUBSTR(CHAR(CURRENT DATE),8,0)||'0101'

And:

SELECT SUBSTR(CHAR(CURRENT DATE),8,0)||'1231

But it does not work till I put the year, but I need the year to be a parameter "not need to change it every year in the Query"

db2 select date sql
1 Answers

You could do:

select
    date('0001-01-01') + year(current date) years - 1 year first_day_of_the_year,
    date('0001-01-01') + year(current date) years - 1 day  last_day_of_the_year
from sysibm.sysdummy1

Demo on DB Fiddle:

FIRST_DAY_OF_THE_YEAR | LAST_DAY_OF_THE_YEAR
--------------------: | -------------------:
           2020-01-01 |           2020-12-31

If you want the results as strings in YYYYMMDD format, you can format the results with to_char():

select
    to_char(date('0001-01-01') + year(current date) years - 1 year, 'yyyymmdd') first_day_of_the_year,
    to_char(date('0001-01-01') + year(current date) years - 1 day, 'yyyymmdd')  last_day_of_the_year
from sysibm. sysdummy1
user_106767160
Related