How to mask date format in sql?

Question

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 1

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>

Answer 2

You can use DATEDIFF function. Here is the code

SELECT DATEDIFF(DAY, CONVERT(DATETIME, '2015-05-19 10:40:14'), CONVERT(DATETIME, ' 2015-05-20 09:21:11'))

Answer 3

Try this

  case
  when status_name.list_value_id=9137981352013344123 then
  (TRUNC(close_date.date_value) - TRUNC(open_date.date_value))
  else
  (TRUNC(sysdate) - TRUNC(open_date.date_value))
  end  as "e2e execution time",