How to select all months of the current year with number of days-CodePudding

I try to create a SQL statement, which selects all month of the current year with number of days.

I don't know where to start.

I got:

SELECT to_char(date, 'YYYY-MM-DD') AS YearMonth
FROM date_table
WHERE YearMonth LIKE ('2021%')

However, I get the output

YearMonth 
2
7
3
9
11

How do I get it to look like this:

And how do I find out how many days the months got?

CodePudding user response：

Using the built in calendar table, and lpad to get leading zeros:

select
lpad(cast(month_of_year as varchar(2)),2,'0'),
count (*)
from
sys_calendar.calendar
where
extract(year from current_date) = year_of_Calendar
group by 1
order by 1

CodePudding user response：

To create the list of months based on today, either as string or numeric:

SELECT
   To_Char(Last(pd), 'mm-dd')   AS mmdd
  ,Extract(MONTH From Last(pd)) AS mm
  ,Extract(DAY From Last(pd))   AS dd
FROM 
 ( -- EXPAND ON requires FROM and TRUNC materializes the FROM avoiding error
   -- "9303 EXPAND ON clause must not be specified in a query expression with no table references."
   SELECT Trunc(Current_Date, 'YYYY') AS start_date
 ) AS dt
EXPAND ON PERIOD(start_date, start_date   INTERVAL '1' YEAR) AS pd
       BY INTERVAL '1' MONTH

This can easily be wrapped in a Derived Table or CTE to be used in a Select. But depending on what you really want to achieve there might be better solutions, e.g. applying EXPAND ON directly or using Time Series Aggregation

CodePudding user response：

try this

SELECT lpad(cast(to_char(date,'MM') as varchar(2)),2,'0') AS YearMonth,
EXTRACT(DAY FROM LAST_DAY(date)) days_in_month
FROM date_table
WHERE to_char(date, 'YYYY') ='2021'

see (https://downloads.teradata.com/forum/general/get-the-number-of-days-in-a-month)