How to filter by date in timestamp field?

Question

SQL

I have a table in hive: table (id, date)
date in format 'yyyy-MM-dd HH:mm:ss'
how to filter for example:
WHERE date = '2020-01-01'

does not return anything
explicit conversion operator required date please help

Answer 1

For MySQL you can do this:

WHERE `date` BETWEEN :date AND :date + INTERVAL 1 DAY

It may be different in other dialects.

Answer 2

WHERE date >= '2020-01-01 00:00:00' and date <= '2020-01-01 23:59:59'

Well, or select the date from the timestamp and compare only with the date.
It is better to present the date in a more universal format: 'YYYYMMDD HH:MM:SS'
And write which server you are using.