Build date from year and month with quarter-CodePudding

I have a table with some columns. Unfortunately, I don't have column with classic date format like "YYYY-MM-DD".

I have columns year and month, like:

2021 | 7
2021 | 10
2021 | 1

I want to build date from these columns and extract quarter in format (Q3'21) from this date. Can I do it with PostgreSQL?

I expect two new columns date and quarter:

2021 | 7 | 2021-07-01 | Q3'21
2021 | 10 | 2021-10-01 | Q4'21
2021 | 1  | 2021-01-01 | Q1'21

I tried to build date with simple concatenation gr."year" || '-0' || gr."month" || '-01' as custom_date, but i got wrong values like: 2021-010-01

CodePudding user response：

You an use make_date() to create a date, then use to_char() to format that date:

select t.year, 
       t.month, 
       make_date(t.year, t.month, 1), 
       to_char(make_date(t.year, t.month, 1), '"Q"Q''YY')
from the_table t;

CodePudding user response：

Use MAKE_DATE:

select 
  year, month,
  make_date(year, month, 1) as first_day_of_month,
  'Q' || to_char(make_date(year, month, 1), 'Q') || '''' || 
         to_char(make_date(year, month, 1), 'YY') as quarter
from mytable;