timestamp and date comparison

Database
Visitor

timestamp and date comparison

Hi,

 

I have the below query -

 

select
*
from T1
where
CAST(  sum_dt AS TIMESTAMP(0)) +  (sum_tm - TIME '00:00:00' HOUR TO SECOND)
 > TO_date('02/28/2018 01:00:00', 'MM/DD/YYYY HH24:MI:SS')

sum_dt = 02/28/2018

Sum_tm = 15:15:36

so CAST(  sum_dt AS TIMESTAMP(0)) +  (sum_tm - TIME '00:00:00' HOUR TO SECOND) becomes 02/28/2018 15:15:36. But still the query does not return any data from table. any reason why ?

 

2 REPLIES
Teradata Employee

Re: timestamp and date comparison

To_date() returns a date.  Use To_Timestamp().

Junior Contributor

Re: timestamp and date comparison

There's no reason to use Oracle style TO_DATE/TO_TIMESTAMP, simply write a Standard SQL date literal, which always uses the YYYY-MM-DD HH:MI:SS format:

TIMESTAMP '2018-02-28 01:00:00'

This is also supported by Oracle, btw