How to insert Julian Date in Snowflake Date Datatype field

Question

My data is in below format ( Julian):

2020102 2019134

Datatype in Snowflake is Date for this field. How do I insert this in Snowflake using Copy Command.

I am thinking below but I couldn't find a date format for Julian yet. Copy into Table*col1,col2) (select $1,TO_DATE($2,'format') from @%Table

score 1 · Answer 1 · answered Sep 15 '20 at 22:46

Snowflake doesn't currently support the Julian date format, but you can use an expression to convert it to a date. COPY doesn't support all functions yet, but you can do it using an INSERT+SELECT from stage. Here's a simple example:

COPY INTO @~/test
FROM
  (SELECT $1 FROM
   VALUES (2020102),(2019134))
;


CREATE OR REPLACE TABLE t1 (juldate int, caldate date);


INSERT INTO t1
SELECT $1,
       (left($1, 4)||'-01-01')::date + right($1, 3)::integer - 1
FROM @~/test;


SELECT *
FROM t1;

JULDATE CALDATE
2020102 2020-04-11
2019134 2019-05-14

score 0 · Answer 2 · answered Sep 15 '20 at 22:34

Snowflake doesn't have a built-in Julian date converter, nor do I think the values you've provided are actually Julian dates, as the format doesn't typically have a 4-digit year, I don't think. Either way, you're going to need to do some parsing and date-adding. Something like this might work, although I'm not 100% sure what the expected outcome is to confirm:

with x as (
    SELECT '2020102' as julian
  )
SELECT DATEADD(days,SUBSTRING(julian,5,3)::number-1,(SUBSTRING(julian,1,4)||'-01-01')::DATE)
FROM x;

This seems to work, but again, not 100% sure.

How to insert Julian Date in Snowflake Date Datatype field

2 Answers2