Oracle – Casting Timestamp as Date and Comparing Results

dateoracleoracle-11g-r2timestamp

I don‘t really understand what cast(timestamp as date) is doing. I am on Oracle Database 11g Express Edition Release 11.2.0.2.0.

select cast(to_timestamp('20190516 08:00:00', 'yyyymmdd HH24:MI:SS') as date) from dual

Displays the date 20190516 (without time) in the date format I have in my session. 

select to_timestamp('20190516 08:00:00', 'yyyymmdd HH24:MI:SS') from dual;

Shows the date with time in my session format. So far everything as I expected.

So casting seems to remove the time part. Now I want to compare the result of the cast with a date which works in SQL Server but not Oracle.

select case when cast(to_timestamp('20190516 08:00:00', 'yyyymmdd HH24:MI:SS') as date) = to_date('20190516', 'yyyymmdd') then 1 else 0 end as match from dual; -- 0 = not matched
select case when trunc(to_timestamp('20190516 08:00:00', 'yyyymmdd HH24:MI:SS')) = to_date('20190516', 'yyyymmdd') then 1 else 0 end as match from dual; -- 1 = matched

What exacty is returned by cast(to_timestamp('20190516 08:00:00', 'yyyymmdd HH24:MI:SS') as date) when it does not seem to be just the date as indicated by the first select? Why can‘t I compare the result?

As shown I luckily have another solution with trunc(). I would like to know why comparing does not work and if there is a possiblity to get the compare to work with cast. I am porting a view from SQL Server to Oracle and would like to keep as much as possible the same and it is done with cast in SQL Server.

Best Answer

Casting to a DATE datatype from a TIMESTAMP only removes the fractional seconds, therefore your direct to_date('20190516', 'yyyymmdd') comparison is rightly failing. It's just a display issue that has confused you.

Definition of the Oracle DATE datatype (from here):

Valid date range from January 1, 4712 BC to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone.

This DB Fiddle demonstrates.

These queries probably demonstrate best:

-- cast to date, and format with TO_CHAR to show that the time element still exists
select  TO_CHAR(
               cast(to_timestamp('20190516 08:00:00', 'yyyymmdd HH24:MI:SS') as date)
                ,'yyyymmdd HH24:MI:SS') from dual; 

-- demonstrate that the fractional element gets removed 
select cast(
         cast(to_timestamp('20190516 08:00:00.123', 'yyyymmdd HH24:MI:SS.FF')
              as date) 
       as timestamp) from dual;
Related Question