Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, padmanabha.

Asked: August 10, 2022 - 11:03 pm UTC

Last updated: August 11, 2022 - 1:54 pm UTC

Version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 64 bit

Viewed 10K+ times! This question is

You Asked

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?

and Chris said...

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:49


TRUNC 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)
----------
         1


You 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


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.