What is the working of oracle timestamp to data & comparing results?
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.
Casting to a DATE datatype from an oracle TIMESTAMP to date 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 dates 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 data type 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;