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,
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?
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
Extracting the year is simple:
Calendar_Year_Month/100 as cal_year