In a column I have dates - 01-02-2022, 01-03-2022

How can i get this in the below format

ABS_DATE                    Quarter            Month 
01-02-2022                  Q1 2022             02/2022
30-03-2021                  Q1 2021             03/2021
29-12-2020                  Q4 2020             12/2020

CodePudding user response：

You can use the to_char() function to convert your dates to whatever format you need, including quarters:

to_char(abs_date, 'DD-MM-YYYY') as abs_date
to_char(abs_date, '"Q"Q YYYY') as quarter
to_char(abs_date, 'MM/YYYY') as month

The quoted "Q" is a character literal, not to be confused with the unquoted Q which is the element for "Quarter of year".

Demo using a CTE for sample data:

with your_table (abs_date) as (
  select date '2022-02-01' from dual
  union all
  select date '2021-03-30' from dual
  union all
  select date '2020-12-29' from dual
)
select abs_date as raw_date,
  to_char(abs_date, 'DD-MM-YYYY') as abs_date,
  to_char(abs_date, '"Q"Q YYYY') as quarter,
  to_char(abs_date, 'MM/YYYY') as month
from your_table

db<>fiddle