About this question
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 is 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 exactly 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 possibility 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.
