CODEWITHSUNDEEP
snowflake-cloud-data-platform
jaycode
jaycode

Reputation: 2958

WHERE date query returns "Date 'xxx' is not recognized" in Snowflake

This query:

select * from weather_effects order by date desc limit 5;

Returns this:

enter image description here

Now I want to select by date

select * from weather_effects w where w.date = '2019-12-13';

But I got this error:

100040: Date '2010-10-03 04:32:52, 2011-01-10 08:41:37, 2011-01-13 21:05:23, 2011-01-24 21:16:55, 2011-02-19 04:16:59, 2011-05-01 06:41:41, 2011-05-08 02:15:23, 2011-05-11 05:44:18, 2011-07-17 04:52:19, 2011-11-19 03:46:59, 2011-11-30 04:14:01, 2011-12-23 23:41:42, 2011-12-26 05:23:42, 2012-02-03 07:32:03, 2012-02-03 08:05:14, 2012-04-24 01:08:08, 2012-05-01 01:42:03, 2012-06-03 06:43:34, 2012-07-29 07:52:24, 2012-08-02 02:40:10, 2012-09-01 03:15:01, 2012-12-19 12:43:06, 2012-12-24 22:08:29, 2013-01-01 08:43:20, 2013-01-12 10:19:29, 2013-02-25 06:33:35, 2013-03-20 06:37:46, 2013-03-31 06:44:18, 2013-04-03 11:09:52, 2013-04-20 05:20:52, 2013-04-22 14:47:54, 2013-04-27 03:25:01, 2013-05-04 05:47:48, 2013-05-26 13:15:51, 2013-06-24 03:34:11, 2013-06-27 03:47:52, 2013-06-28 04:04:11, 2013-07-08 03:12:15, 2013-07-10 04:43:07, 2013-07-22 04:38:48, 2013-07-25 19:58:43, 2013-07-29 04:40:02, 2013-08-18 02:37:00, 2013-09-11 07:52:43, 2013-09-23 06:41:21, 2013-09-30 04:39:22, 2013-10-05 07:00:22, 2013-12-24 09:53:12, 2013-12-29 04:23:24, 2013-12-29 06:24:32, 2014-02-07 11:40:35, 2014-02-15 05:57:23' is not recognized

enter image description here

Please help.

Upvotes: 0

Views: 2229

Answers (2)

Mike Walton
Mike Walton

Reputation: 7339

Can you verify the data type of the field "DATE" in that table? It looks like you've got some long string somewhere in your table that are not only NOT dates, but appear to be timestamps...and a string of them.

Try something like this:

select * from weather_effects where TRY_TO_DATE("DATE") = '2019-12-13'::DATE;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I would suggest either:

where w.date::date = '2019-12-13'::date

Or:

where w.date >= '2019-12-13'::date and
      w.date < '2019-12-14'::date

Upvotes: 1

Related Questions