'Number of days in a month in DB2

Is there a way to find the number of days in a month in DB2. For example I have a datetime field which I display as Jan-2020, Feb-2020 and so on. Based on this field I need to fetch the number of days for that month. The output should be something like below table,

I'm using the below query

select reportdate, TO_CHAR(reportdate, 'Mon-YYYY') as textmonth from mytable

Expected output

ReportDate       textMonth   No of Days
1-1-2020 08:00   Jan-2020      31
1-2-2020 09:00    Feb-2020     29
12-03-2020 07:00  Mar-2020     31

db2

Solution 1:^[1]

Try this:

/*
WITH MYTABLE (reportdate) AS 
(
VALUES
  TIMESTAMP('2020-01-01 08:00:00')
, TIMESTAMP('2020-02-01 09:00:00')
, TIMESTAMP('2020-03-12 07:00:00')
)
*/
SELECT reportdate, textMonth, DAYS(D + 1 MONTH) - DAYS(D) AS NO_OF_DAYS
FROM
(
SELECT 
  reportdate, TO_CHAR(reportdate, 'Mon-YYYY') textMonth
, DATE(TO_DATE('01-' || TO_CHAR(reportdate, 'Mon-YYYY'), 'dd-Mon-yyyy')) D
FROM MYTABLE
);

Solution 2:^[2]

Db2 has the function DAYS_TO_END_OF_MONTH and several others which you could use. Based on your month input, construct the first day of the month. This should be something like 2020-01-01 for Jan-2020 or 2020-02-01 for Feb-2020. Follow the link for several other conversion functions which allow you to transform between formats and to perform date arithmetics.

Solution 3:^[3]

convert your column to a proper date and try this: day(last_day(date_column))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2	data_henrik
Solution 3	Sathish Thiruvael Ravi

'Number of days in a month in DB2

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]