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

0
votes

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.

2
snowflake-cloud-data-platform

2 Answers

1
votes

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
1
votes

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;