CODEWITHSUNDEEP
sqltimestampsnowflake-cloud-data-platform
Oscar Gutierrez
Oscar Gutierrez

Reputation: 25

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

Upvotes: 0

Views: 3276

Answers (5)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26078

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

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95082

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'

Upvotes: 1

Greg Pavlik
Greg Pavlik

Reputation: 11076

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.

Upvotes: 3

Praise Dare
Praise Dare

Reputation: 550

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

Upvotes: 0

Sasank Sharma
Sasank Sharma

Reputation: 183

Please execute the query including seconds like below

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

Upvotes: 0

Related Questions