I am trying compare dates.
Following SQL returns one row
SELECT COUNT(1) FROM (
SELECT 1 FROM DUAL WHERE
TRUNC(CAST(CAST('13-JUN-22 05.47.49.000000000 PM' AS TIMESTAMP) AS DATE)) = TO_DATE('13-JUN-22','DD-MON-YY')
);where as the same query without TRUNC returns zero rows
SELECT COUNT(1) FROM (
SELECT 1 FROM DUAL WHERE
(CAST(CAST('13-JUN-22 05.47.49.000000000 PM' AS TIMESTAMP) AS DATE)) = TO_DATE('13-JUN-22','DD-MON-YY')
);I believe (maybe I am wrong) that although the CAST convert the TIMESTAMP into DATE, in the first SQL, nevertheless ,there is a TIME part that still remains in it, which is eliminated by the TRUNC .
Is this conclusion correct and what is value of the TIME part?
DATEs in Oracle Database always have a time component. When you cast a TIMESTAMP to a DATE, you get the same datetime value with any fractional seconds and time zone information removed. Hours/minutes/seconds are preserved:
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select
cast (
timestamp'2022-06-13 05:47:49.123456 GMT' as date
) dt
from dual;
DT
--------------------
13-JUN-2022 05:47:49TRUNC rounds down the input to the units specified. This defaults to the day, so you get the date with a time of midnight. TRUNC also always returns a DATE value, even if the input is a TIMESTAMP, so there's no need to CAST to a date too:
select count(1) from (
select 1 from dual
where trunc (
timestamp'2022-06-13 05:47:49.000000'
) = to_date ( '13-JUN-22','DD-MON-YY' )
);
COUNT(1)
----------
1You should also use a timestamp literal (as above) or explicitly set the format mask - ideally with to_timestamp. This avoids changes to the session settings affecting the results like this:
select value from nls_session_parameters
where parameter = 'NLS_TIMESTAMP_FORMAT';
VALUE
----------------------------------------------------------------
DD-MON-YYYY HH24.MI.SSXFF
select
cast (
'13-JUN-22 05.47.49.000000000 PM' as timestamp
)
from dual;
ORA-01830: date format picture ends before converting entire input string