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

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

sqltimestampsnowflake-cloud-data-platform


Solution 1:[1]

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.

Solution 2:[2]

Please execute the query including seconds like below

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

Solution 3:[3]

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

Solution 4:[4]

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'

Solution 5:[5]

SELECT column1, TO_TIMESTAMP(column1, 'mm/dd/yyyy hh:mi') as ts
FROM VALUES
  ('5/9/2022 11:09'),
  ('5/12/2022 12:09'),
  ('5/24/2022 23:09');
COLUMN1 TS
5/9/2022 11:09 2022-05-09 11:09:00.000
5/12/2022 12:09 2022-05-12 12:09:00.000
5/24/2022 23:09 2022-05-24 23:09:00.000

so you "this is for a large data set" does not make sense..

do you mean you data set has mixed formatted data?

at which point the TRY_ functions can be used, to try things in rank order:

SELECT 
    column1 
    ,TRY_TO_TIMESTAMP(column1, 'mm/dd/yyyy hh:mi') as ts_a
    ,TRY_TO_TIMESTAMP(column1, 'mm/dd/yyyy') as ts_b
    ,TRY_TO_TIMESTAMP(column1, 'mm/dd/yyyy hh:mi:ss.ff6') as ts_c
    ,coalesce(ts_a, ts_b, ts_c) as ts
FROM VALUES
('5/9/2022 11:09'),
('5/12/2022'),
('5/24/2022 23:09'),
('5/24/2022 23:09:59.99999');
COLUMN1 TS_A TS_B TS_C TS
5/9/2022 11:09 2022-05-09 11:09:00.000 null null 2022-05-09 11:09:00.000
5/12/2022 null 2022-05-12 00:00:00.000 null 2022-05-12 00:00:00.000
5/24/2022 23:09 2022-05-24 23:09:00.000 null null 2022-05-24 23:09:00.000
5/24/2022 23:09:59.99999 null null 2022-05-24 23:09:59.999 2022-05-24 23:09:59.999

and that can be merge into a single command to clean that up:

SELECT 
    column1 
    ,coalesce(TRY_TO_TIMESTAMP(column1, 'mm/dd/yyyy hh:mi'), TRY_TO_TIMESTAMP(column1, 'mm/dd/yyyy'), TRY_TO_TIMESTAMP(column1, 'mm/dd/yyyy hh:mi:ss.ff6')) as ts
FROM VALUES
('5/9/2022 11:09'),
('5/12/2022'),
('5/24/2022 23:09'),
('5/24/2022 23:09:59.99999');
COLUMN1 TS
5/9/2022 11:09 2022-05-09 11:09:00.000
5/12/2022 2022-05-12 00:00:00.000
5/24/2022 23:09 2022-05-24 23:09:00.000
5/24/2022 23:09:59.99999 2022-05-24 23:09:59.999

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Greg Pavlik
Solution 2 Sasank Sharma
Solution 3 Praise Dare
Solution 4 Thorsten Kettner
Solution 5 Simeon Pilgrim

Related Questions