How to convert/extract from YYYYMM to year and name of Month? Teradata SQL

So i got the date format in YYYYMM (eg. 201910 for Oct 2019), and what I want to have is 2019 and October (in full month name). Year number is easy, but to get the month name is a bit challenging.

In my data Calendar_Year_Month is the available field in the format of INT eg. 201910. I firstly converted to STRING and then adding just a random day 01 to become 20191001, then casting it to DATE format as Year_Month_Date.

CAST(Calendar_Year_Month*100+01 AS VARCHAR(8)) AS Year_Month,
CAST(Year_Month AS DATE FORMAT 'YYYYMMDD')AS Year_Month_Date,

​x
 
CAST(Calendar_Year_Month*100+01 AS VARCHAR(8)) AS Year_Month,CAST(Year_Month AS DATE FORMAT 'YYYYMMDD')AS Year_Month_Date,​

I tried to use `TO_DATE(Year_Month_Date,’Month’) to get the Month name, however, it seems not working.

Any other options to achieve?

Answer

You were close:

CAST the int to a date and then apply TO_CHAR:

To_Char(Cast((Calendar_Year_Month-190000) * 100 + 1 AS DATE), 'Month') AS cal_month

 
To_Char(Cast((Calendar_Year_Month-190000) * 100 + 1 AS DATE), 'Month') AS cal_month​

Extracting the year is simple:

Calendar_Year_Month/100 as cal_year

 
Calendar_Year_Month/100 as cal_year​

Advertisement

Answer