CODEWITHSUNDEEP
sqldatesnowflake-cloud-data-platform
Joe
Joe

Reputation: 13101

Snowflake - insert date

I have a value of 12/31/18 and created table in snowflake:

create table my_date (a date);

insert into my_date values ('12/31/18');

select * from my_date;

Result: 0018-12-31

I want to get: 2018-12-31

I saw about 2 number format:
https://docs.snowflake.net/manuals/sql-reference/parameters.html#label-two-digit-century-start

but not sure if this is specification of a column type or data needs to be transformed before the insert?

Upvotes: 1

Views: 5770

Answers (1)

Veikko
Veikko

Reputation: 3610

The parameter two_digit_century_start seems not to be used when parameter date_input_format is set to AUTO. You can get your example working correctly by setting the date format with a parameter ("alter session..." statement on line 2 below). Your complete working example would look like this:

create table my_date (a date);
alter session set DATE_INPUT_FORMAT = 'MM/DD/YY';
insert into my_date values ('12/31/18');
select * from my_date;

This results in 2018-12-31.

Snowflake best-practices recommend to specify the format explicitly with to_date(value, 'format') or by setting the format in parameters. You can find the best practices for date/time functions from Snowflake documentation here: https://docs.snowflake.net/manuals/user-guide/date-time-input-output.html#date-time-function-format-best-practices

Upvotes: 4

Related Questions