Hive/SparkSQL - Type Coercion from date to timestamp in an expression
<p>When I run Hive queries in Spark SQL, <strong>LHS</strong> (<code>timestamp</code>) <code>&lt;=</code> <strong>RHS</strong> (<code>date</code>) for same value <code>'2013-09-30'</code> <strong>is not treating as equal</strong>. Whereas <code>CAST</code> of <code>TIMESTAMP</code> in RHS works well.</p>

<pre><code>&gt; SELECT CASE
           WHEN CAST(ADD_MONTHS(CAST('2013-09-30' AS DATE), +1) AS TIMESTAMP) &lt;= ADD_MONTHS(CAST('2013-09-30' AS DATE), +1) 
            THEN 'less than or equal'
           ELSE 'greater'
       END AS TIMESTAMP_LTE_DATE
FROM
VALUES(1);
+---------------------+--+
| TIMESTAMP_LTE_DATE  |
+---------------------+--+
| greater             |
+---------------------+--+
</code></pre>

<p>We have so many expressions with <code>DATE</code> and <code>TIMESTAMP</code> combinations more over all the queries will generate dynamically. So, difficult to find and <code>CAST</code> columns or values in all the places.</p>

<p>Is there a way to Automatically convert RHS/LHS in expression to the higher data type(<strong>Type Coercion</strong>) in Hive/SparkSQL ? </p>

<p>Tried similar query in Oracle 11g as well and it works fine which goes to <code>equal</code>.</p>

<p>Appreciate your help!!</p>