How do I solve this error I am receiving from snowflake stored procedure
Error: Uncaught exception of type 'STATEMENT_ERROR' on line xx at position xx : Can't parse '0000-00-00 00:00:00' as timestamp with format 'AUTO'.
I tried conversion using following statements at SELECT
1: TO_TIMESTAMP('01-25-2019 23:25:11.120', 'MM-DD-YYYY HH24:MI:SS.FF');
2: TO_DATE(LEFT(timestamp, 10), 'YYYY-MM-DD')
Nothing works here. I know DateTime here is '0000-00-00 00:00:00', So if there is any possible workaround.
TIA
CodePudding user response：
That value is not a recognized timestamp by Snowflake.
So a value with format like this:
select '2022-01-01 00:00:00'::timestamp;
works fine while your value:
select '0000-00-00 00:00:00'::timestamp;
throws:
Timestamp '0000-00-00 00:00:00' is not recognized
For DATE and TIMESTAMP data, Snowflake recommends using years between 1582 and 9999. Snowflake accepts some years outside this range, but years prior to 1582 should be avoided due to limitations on the Gregorian Calendar.
For more information have a look here.
CodePudding user response：
As Sergiu already mentioned Snowflake recommends using years between 1582 and 9999.
More importantly, the first second of the year 0 should be 0000-01-01 00:00:00. So '0000-00-00 00:00:00' is not a valid date/time.
select dateadd( 'seconds', -1, TO_TIMESTAMP('0000-01-01 00:00:00' ));
-0001-12-31 23:59:59.000
To prevent getting errors on this "invalid timestamp", you can use TRY_TO_TIMESTAMP function:
select TRY_TO_TIMESTAMP('0000-01-01 00:00:00'); -- returns NULL