Create date in format YYYY-MM and add 0 in case month < 10

I want to create a date in the format YYYY-MM using postgresSQL.
The month should always be two digits.
For example if it is August 2020 the result should be 2020-08 and not 2020-8.

Therefore, I tried to add the 0 in front of the 8 using a CASE statement looking like this:

DB-Fiddle

SELECT
(CASE WHEN DATE_PART('month', date '2020-08-01 00:00:00') <10 
THEN CONCAT('0',DATE_PART('month', date '2020-08-01 00:00:00'))
ELSE 1 END) AS year_month_value

​x
 
SELECT(CASE WHEN DATE_PART('month', date '2020-08-01 00:00:00') <10 THEN CONCAT('0',DATE_PART('month', date '2020-08-01 00:00:00'))ELSE 1 END) AS year_month_value​

However, with this query I get error:

ERROR:  CASE types integer and text cannot be matched
LINE 3: THEN CONCAT('0',DATE_PART('month', date '2020-08-01 00:00:00...

 
ERROR:  CASE types integer and text cannot be matchedLINE 3: THEN CONCAT('0',DATE_PART('month', date '2020-08-01 00:00:00...​

I have no clue how I need to modify the query to make it work.
Do you have any idea?

Answer

Use to_char():

General

select to_char(dateval, 'YYYY-MM')

 
select to_char(dateval, 'YYYY-MM')​

Example DB-Fiddle

SELECT to_char(date '2020-08-01 00:00:00','YYYY-MM')

 
SELECT to_char(date '2020-08-01 00:00:00','YYYY-MM')​

Advertisement

Answer