'Build date from year and month with quarter

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

sqlpostgresql


Solution 1:[1]

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;

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 Thorsten Kettner

Related Questions