How in Oracle to calculate difference between current date and column date?-CodePudding

I have column INACTIVE_TIME where I need to put integer number (how many days pass from some date), to represent difference between current date and column date ("LOAD_DATE" column).

In column LOAD_DATE I have data in format 03-AUG-22 03.55.57.587481000 PM.

I understand I need to get current date and than minus date from LOAD_DATE column. I try something like this:

SELECT COUNT(*)
FROM TABLE_NAME
WHERE ((TO_DATE(SYSDATE,'DD/MM/YYYY')-(TO_DATE(LOAD_DATE,'DD/MM/YYYY'));

CodePudding user response：

It is about load_date column's datatype, not the way you see that value (because it can be changed). I presume (and hope) it is timestamp; you aren't storing it as a string, are you?

If so, then you don't apply to_date to sysdate - it is a function that already returns date datatype.

Setting timestamp and date format (just to know what is what; your tool displays different format, with month name and two-digits year) (you don't have to do that).

SQL> alter session set nls_timestamp_format = 'dd.mm.yyyy hh24:mi:ss.ff9';

Session altered.

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

Sample table; note datatype:

SQL> create table table_name (load_date timestamp);

Table created.

SQL> insert into table_name values (systimestamp);

1 row created.

Query you're looking for (at least, I think so):

SQL> select load_date, sysdate,
  2    --
  3    sysdate - load_date as diff
  4  from table_name;

LOAD_DATE                      SYSDATE             DIFF
------------------------------ ------------------- ------------------------------
04.08.2022 10:22:58.101062000  04.08.2022 10:23:08  000000000 00:00:09.898938

SQL>

To extract days, hours, minutes ... whatever, you can use that function - extract. For example:

SQL> select load_date,
  2    sysdate,
  3    sysdate - load_date as diff,
  4    --
  5    extract (day    from sysdate - load_date) as diff_days,
  6    extract (hour   from sysdate - load_date) as diff_hours,
  7    extract (minute from sysdate - load_date) as diff_minutes
  8  from table_name;

LOAD_DATE                 SYSDATE             DIFF                        DIFF_DAYS DIFF_HOURS DIFF_MINUTES
------------------------- ------------------- -------------------------- ---------- ---------- ------------
04.08.22 10:22:58,101062  04.08.2022 11:51:32  000000000 01:28:33.898938          0          1           28

SQL>

CodePudding user response：

Your Where clause isn't saying anything. What are you wanting it to filter?

Try Where (sysdate - table_name.load_date) > 0

This might not be what you want, but you need to tell the query something else