Mohammed Rahmathulla Mohammed Rahmathulla - 6 months ago 11
SQL Question

How to mask date format in sql?

There is open date

2015-05-19 10:40:14
and close date
2015-05-20 09:21:11


when I subtract them I am getting (close_date.date_value - open_date.date_value)
some
9.45104166666666666666666666666666666667E-01
value

I want to ignore the time 10:40:14 and 09:21:11 from 2 dates
similarly I am subtracting (SYSDATE - open_date.date_value) and get the number of days in number when I subtract 2 dates

Could anyone help me resolving this problem

case
when s then
(close_date.date_value - open_date.date_value)
else
(SYSDATE - open_date.date_value)
end as "dd",

Answer

From performance point of view, I would not use TRUNC as it would suppress any regular index on the date column. I would let the date arithmetic as it is, and ROUND the value.

For example,

SQL> SELECT SYSDATE - to_date('2015-05-20 09:21:11','YYYY-MM-DD HH24:MI:SS') diff,
  2         ROUND(
  3            SYSDATE - to_date('2015-05-20 09:21:11','YYYY-MM-DD HH24:MI:SS')
  4              ) diff_round
  5  FROM dual;

      DIFF DIFF_ROUND
---------- ----------
29.1248264         29

SQL>