How to convert a column value which is like 20220401 of number datatype to 2022-04-01 in snowflake

Question

I have a column of number data type in snowflake that stores values like 20220401, now I need to convert it into date format. Could someone please help

Answer 1

select to_date('02/14/2014', 'MM/DD/YYYY');

create table number_to_date(numbers integer);
insert into number_to_date values (20220401);
select to_date(to_char(numbers),'YYYYMMDD') from number_to_date;

More details: https://docs.snowflake.com/en/sql-reference/functions/to_date.html#examples

Answer 2

You can use to_date, but it works on char datatype, so first need to convert your column to char/string.

with data_cte(col1) as
(select 20220401::number)
select to_date(col1::string,'yyyymmdd') from data_cte

Answer 3

Yet more way's of seeing/saying the same thing:

to_date allows you to define format string

select column1 as raw_str, 
    to_date(column1, 'yyyymmdd') as as_date
from values
    ('19900101'),
    ('20220511');

gives:

RAW_STR	AS_DATE
19900101	1990-01-01
20220511	2022-05-11

if you have string that sometimes are not following the format pattern you might want to use try_to_date as this will not generate an error:

select column1 as raw_str, 
    try_to_date(column1, 'yyyymmdd') as as_date
from values
    ('19900101'),
    ('not a date'),
    ('20220511');

RAW_STR	AS_DATE
19900101	1990-01-01
not a date	null
20220511	2022-05-11

But given you said "number input"

select column1 as raw_number, 
    to_date(column1::text, 'yyyymmdd') as as_date
from values
    (19900101),
    (20220511);

now we have numbers, but to_date wants text, so we cast it to text, so the parser can be used.

giving:

RAW_NUMBER	AS_DATE
19,900,101	1990-01-01
20,220,511	2022-05-11

Now if you use TO_TIMESTAMP which accepts the same format string, the naked number by itself it will think you mean epoch seconds, and give you wacky tiny number, like:

select column1 as raw_number, 
    to_timestamp(column1) as as_timestamp
from values
    (19900101),
    (20220511);

RAW_NUMBER	AS_TIMESTAMP
19,900,101	1970-08-19 07:48:21.000
20,220,511	1970-08-23 00:48:31.000

which is all to say, cast it to string via ::text or the likes, and specify your format, so that you can handle yyyymmdd or yyyyddmm or how every your data is.