Create a date from Year and Month on a Select query

I'm working on Vertica

I have a problem, looking really easy, but I can't find a way to figure it out.

From a query, I can get 2 fields "Month" and "Year". What I want is to automatically select another field, Date, that I'd build being '01/Month/Year' (as the sql Date format). The goal is :

What I have

SELECT MONTH, YEAR FROM MyTable

Output :

01   2020
11   2019
09   2021

What I want

SELECT MONTH, YEAR, *answer* FROM MyTable

Output :

01   2020   01-01-2020
11   2019   01-11-2019
09   2021   01-09-2021

Sorry, it looks like really dumb and easy, but I didn't find any good way to do it. Thanks in advance.

Solution

Don't use string operations to build dates, you can mess up things considerably: Today could be: 16.07.2021 or 07/16/2021, or also 2021-07-16, and, in France, for example: 16/07/2021 . Then, you could also left-trim the zeroes - or have 'July' instead of 07 ....

Try:

WITH
my_table (mth,yr) AS (
            SELECT  1, 2020
  UNION ALL SELECT 11, 2019
  UNION ALL SELECT  9, 2021
)
SELECT
  yr
, mth
, ADD_MONTHS(DATE '0001-01-01',(yr-1)*12+(mth-1)) AS firstofmonth
FROM my_table
ORDER BY 1,2;
  yr  | mth | firstofmonth 
------+-----+--------------
 2019 |  11 | 2019-11-01
 2020 |   1 | 2020-01-01
 2021 |   9 | 2021-09-01