SQL: implicit CASTing between DATEs and DATETIMEs

[astefan]

We have a hard CASTing rule for DATEs and DATETIMEs, ie CAST(CAST(date AS STRING) AS DATE) will fail with SqlIllegalArgumentException[cannot cast [2004-06-04T00:00:00.000Z] to [date]: Text '2004-06-04T00:00:00.000Z' could not be parsed, unparsed text found at index 10] because a date and a time is not actually a date.
Should we loosen this hard rule for DATEs and DATETIMEs? Should we do implicit CASTing?

[elasticmachine]

Pinging @elastic/es-search (:Search/SQL)

[matriv]

master : 5863b27
7.x : 6b60085