SQL - Date format conversion issue (AUTO to 'MM/DD/YYYY')-CodePudding

In Snowflake, I have a column in a created table called "Date1", that has dates formatted as AUTO (ex. 2022-06-17). I am trying to query that column, but need the date formatted as 'MM/DD/YYYY', yet everything I've tried returns an error of some kind.

When I try date(Date1, 'MM/DD/YYYY) the error says that it can't parse 2022-06-17 for MM/DD/YYYY. When I try to_date(Date1 [MM/DD/YYYY]) or try_to_date(Date1 [MM/DD/YYYY]) the error says it doesn't recognize MM.

Any thoughts?

CodePudding user response：

You should try to provide correct format to match 2022-06-17 literal:

SELECT TRY_TO_DATE(Date1, 'YYYY-MM-DD')
FROM tab_name;

CodePudding user response：

If you're trying to display the date using a specific format, you're converting to a varchar rather than a date:

select to_varchar(current_date, 'MM/DD/YYYY');

If you're trying to compare a column with a date to a formatted string in MM/DD/YYYY format then:

select current_date = try_to_date('08/04/2022', 'MM/DD/YYYY');