How can I change a Number-type column to a Date-type in Snowflake?-CodePudding

I am using Snowflake and have a number-type column with a YYYYMM format (for example, a row with 202207 = July 2022).

I would like to convert this into a date format, preferably the same formatting as YYYYMM.

I have tried to write various queries like:

SELECT TO_DATE(report_month)
FROM table

SELECT TO_TIMESTAMP(report_month)
FROM table

But they don’t work. I am new to Snowflake, so is changing a numbers-type column to a Date-type (YYYYMM) even possible?

I hope this makes sense, and thank you for the help!

CodePudding user response：

Can you try this?

SELECT
   YEAR(PERIOD)||MONTH(PERIOD)
FROM
   (SELECT
      DATE(TO_DATE(report_month, 'YYYYMM') AS PERIOD
   FROM
      table)

CodePudding user response：

Here it is:

select to_date('201207', 'YYYYMM');