CODEWITHSUNDEEP
sqldatabasedateselectsnowflake-cloud-data-platform
Ricky Prince Gouety
Ricky Prince Gouety

Reputation: 49

How can i make a field with a datatype Varchar be a date in Snowflake?

I am using Snowflake and I have a field with a datatype VARCHAR and the values in that field are for example: 2/10/17, 9/7/18, 1/23/19.

I trying to convert that field into a Date using this script:

select To_Date(Field_name) from CONCUR

However i get this message: Date '' is not recognized

Upvotes: 1

Views: 68

Answers (2)

GMB
GMB

Reputation: 222402

You need a format specification as a second argument to to_date() (otherwise it defaults to session parameter DATE_INPUT_FORMAT, which is probably not what you want):

to_date(field_name, 'MM/DD/YYYY')

You may also want to use try_to_date(), that returns null when the conversion fails rather than raising an error as to_date() does.

Upvotes: 1

Palash Chatterjee
Palash Chatterjee

Reputation: 196

To_date should be used with the format like below

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

https://docs.snowflake.com/en/sql-reference/functions/to_date.html

Thanks Palash

Upvotes: 0

Related Questions