Substracting dates in Oracle
-
I have a problem substracting dates in Oracle. I tested the following: Create a new table with a date field Insert a register using sysdate Then a select (sysdate - dateField) doesn't return 0 as expected. In the SQL Developer it returns an empty field. How can I solve that? Sometimes, maybe depending on the time difference, it works and returns a value (really, I need (sysdate - datefield) * 24*60*60
Regards, Diego F.
-
I have a problem substracting dates in Oracle. I tested the following: Create a new table with a date field Insert a register using sysdate Then a select (sysdate - dateField) doesn't return 0 as expected. In the SQL Developer it returns an empty field. How can I solve that? Sometimes, maybe depending on the time difference, it works and returns a value (really, I need (sysdate - datefield) * 24*60*60
Regards, Diego F.
To break the diff between 2 dates into days, hours, minutes, sec -- you can use the following: select to_char( created, 'dd-mon-yyyy hh24:mi:ss' ), trunc( sysdate-created ) "Dy", trunc( mod( (sysdate-created)*24, 24 ) ) "Hr", trunc( mod( (sysdate-created)*24*60, 60 ) ) "Mi", trunc( mod( (sysdate-created)*24*60*60, 60 ) ) "Sec", to_char( sysdate, 'dd-mon-yyyy hh24:mi:ss' ), sysdate-created "Tdy", (sysdate-created)*24 "Thr", (sysdate-created)*24*60 "Tmi", (sysdate-created)*24*60*60 "Tsec" from all_users where rownum < 50 /
Regards, Satips.:rose:
-
To break the diff between 2 dates into days, hours, minutes, sec -- you can use the following: select to_char( created, 'dd-mon-yyyy hh24:mi:ss' ), trunc( sysdate-created ) "Dy", trunc( mod( (sysdate-created)*24, 24 ) ) "Hr", trunc( mod( (sysdate-created)*24*60, 60 ) ) "Mi", trunc( mod( (sysdate-created)*24*60*60, 60 ) ) "Sec", to_char( sysdate, 'dd-mon-yyyy hh24:mi:ss' ), sysdate-created "Tdy", (sysdate-created)*24 "Thr", (sysdate-created)*24*60 "Tmi", (sysdate-created)*24*60*60 "Tsec" from all_users where rownum < 50 /
Regards, Satips.:rose:
Could you try that basic sample so I see what is the result? First create that table: CREATE TABLE TB_TEST ( ID NUMBER(2, 0) NOT NULL, DATEFIELD DATE , CONSTRAINT TB_TEST_PK PRIMARY KEY ( ID ) ) Now, I insert a register: insert into tb_test values (1, sysdate) commit; And then, the select query: select (sysdate-datefield) from tb_test where id=1 What is the result?
Regards, Diego F.