2

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.

2

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.