I am looking for a processing friendly method to create a date field from 2 VARCHAR fields (year & month) such that the output is always the 1st of input month.

Output Format

• %Y-%m-%d where %d is always 01

Current Fields

1. Field c_year (VARCHAR) in format ‘FY##’ (FY22, FY23, FY24, etc)
2. Field ‘c_month’ (VARCHAR) in format 3 letter abbreviation (Jan, Feb, Mar)

I’ve put a few methods together but they seem run extremely slow.

1. DATEPARSE with nested CONCAT(SUBSTR()) to get 4 digit year and CASE statement (12 checks) for month.
2. DATEPARSE with LPAD and CASE etc

I imagine there’s a better way