Receiving error Timestamp '5/9/2022 11:09' is not recognized-CodePudding

Gives this error when trying to convert this date format in snowflake.

SELECT TO_TIMESTAMP('5/9/2022 11:09')

Timestamp '5/9/2022 11:09' is not recognized

CodePudding user response：

You need to provide a format string:

SELECT TO_TIMESTAMP('5/9/2022 11:09', 'DD/MM/YYYY HH:MI');

-- Or if US based date format:
SELECT TO_TIMESTAMP('5/9/2022 11:09', 'MM/DD/YYYY HH:MI');

This is required because 5/9/2022 is ambiguous. In Europe it's September 5. In the US it's May 9.

CodePudding user response：

Please execute the query including seconds like below

SELECT TO_TIMESTAMP('5/9/2022 11:09:00');

CodePudding user response：

Previous MySQL versions allowed for the use of a "relaxed" date expression. This means that you could use any delimiters at all for the date parts, e.g. 2004@04@16, 2022=09=17, 1968.04.22, were all considered to be valid.

But as of MySQL 8.0.29, this is no longer allowed. You now have to use a - (hyphen) as the delimiter.

You can read the reference manual for more information

CodePudding user response：

You are using an arbitrary timestamp format hoping the DBMS will guess what it means. I must admit I can't. Is this May 9 or September 5?

You may be lucky that the DBMS accepts this format and even guesses right. But instead if relying on luck, you should give the DBMS the information it needs, i.e. the format. Or better still use a timestamp literal right away:

SELECT TIMESTAMP '2022-05-09 11:09:00'