'Converting a four digit month year string to date in Snowflake

So I have some data as follows:

data
1203
0323
0101
1005
1130
0226

So in every case, the first two digits are the month and the last two digits are the year. Is there anyway to easily map these to date for without making it an outright lookup table?

Here's what I am seeking

data date
1203 12/01/2003
0323 03/01/2023
0101 01/01/2001
1005 10/01/2005
1130 11/01/2030
0226 02/01/2026

In every case, I would like the day to be the first of that month.

sqldatesnowflake-cloud-data-platform


Solution 1:[1]

Much the same as Tim's answer:

You want to cast from number to text (and if you data is text skip that, BUT if it's variant (from JSON or XML) you still need to cast to TEXT), then parse with TO_DATE as MM month, YY year in 2 column (format tokens). And you will get the 1st of each of those months

SELECT 
    column1, 
    to_date(column1::text, 'MMYY')
FROM VALUES
    (1203),
    (0323),
    (0101),
    (1005),
    (1130),
    (0226);

giving:

COLUMN1 TO_DATE(COLUMN1::TEXT, 'MMYY')
1203 2003-12-01
323 2023-03-01
101 2001-10-01
1005 2005-10-01
1130 2030-11-01
226 2026-02-01

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Simeon Pilgrim

Related Questions