Concatenate Year and Month-CodePudding

I have 2 date fields, surv_year (int) and surv_month (text).

Example outputs:

surv_year surv_month
2022      January
2022      August

Is there anyway to concatenate the 2 and change the month from a text to a number and have my final output as YYYYMM with my example above showing 202201 and 202208?

CodePudding user response：

You can convert the two values to a proper date using the to_date() function:

select to_date(concat(surv_month, ' ', surv_year), 'Month yyyy')
from the_table

will return a date on the first day of that month.

This date can be formatted using the to_char() function to the format that you want:

select to_char(to_date(concat(surv_month, ' ', surv_year), 'Month yyyy'), 'yyyymm')
from the_table