Home » SQL & PL/SQL » SQL & PL/SQL » difference between dates
difference between dates [message #575537] Wed, 23 January 2013 22:53 Go to next message
anilkln
Messages: 7
Registered: August 2012
Junior Member
I am looking to subtract two columns and get the difference.Please suggest


select to_char('06-NOV-2012 20:00','DD-MON-YYYY HH24:MI') - to_char(systimestamp,'DD-MON-YY HH24:MI') from dual;
select to_char('06-NOV-2012 20:00','DD-MON-YYYY HH24:MI') - to_char(systimestamp,'DD-MON-YY HH24:MI') from dual
*
ERROR at line 1:
ORA-01722: invalid number


Re: difference between dates [message #575538 is a reply to message #575537] Wed, 23 January 2013 22:59 Go to previous messageGo to next message
BlackSwan
Messages: 21945
Registered: January 2009
Senior Member
anilkln wrote on Wed, 23 January 2013 20:53
I am looking to subtract two columns and get the difference.Please suggest


select to_char('06-NOV-2012 20:00','DD-MON-YYYY HH24:MI') - to_char(systimestamp,'DD-MON-YY HH24:MI') from dual;
select to_char('06-NOV-2012 20:00','DD-MON-YYYY HH24:MI') - to_char(systimestamp,'DD-MON-YY HH24:MI') from dual
*
ERROR at line 1:
ORA-01722: invalid number





what is answer when subtracting two strings?

what results from subtracting "nonsense" from "ridiculous"?

try below instead

select sysdate - to_date('06-NOV-2012 20:00','DD-MON-YYYY HH24:MI') from dual;
Re: difference between dates [message #575539 is a reply to message #575538] Wed, 23 January 2013 23:09 Go to previous messageGo to next message
anilkln
Messages: 7
Registered: August 2012
Junior Member
Hi swan,
i am looking to get the output in minutes, so i was trying with systimestamp to convert the difference to minutes later
Re: difference between dates [message #575540 is a reply to message #575539] Wed, 23 January 2013 23:14 Go to previous messageGo to next message
BlackSwan
Messages: 21945
Registered: January 2009
Senior Member
>i am looking to get the output in minutes, so i was trying with systimestamp to convert the difference to minutes later

you should ALWAYS avoid implicit datatype conversions; like between character and DATE & between DATE & TIMESTAMP

why do you require fractions of seconds that exist in SYSTIMESTAMP?
Re: difference between dates [message #575541 is a reply to message #575540] Wed, 23 January 2013 23:18 Go to previous messageGo to next message
anilkln
Messages: 7
Registered: August 2012
Junior Member
no i am not bothered about seconds. i want the output to be in minutes. can you help me
Re: difference between dates [message #575542 is a reply to message #575541] Wed, 23 January 2013 23:21 Go to previous messageGo to next message
BlackSwan
Messages: 21945
Registered: January 2009
Senior Member
did you try the SQL I posted?

when you subtract DATE datatype, the units is days; so you need to convert to minutes.
Re: difference between dates [message #575543 is a reply to message #575542] Wed, 23 January 2013 23:30 Go to previous messageGo to next message
anilkln
Messages: 7
Registered: August 2012
Junior Member
Thanks a lot, it worked,
i have doubt here how is sysdate calculating the time here
Re: difference between dates [message #575544 is a reply to message #575543] Wed, 23 January 2013 23:33 Go to previous messageGo to next message
BlackSwan
Messages: 21945
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: difference between dates [message #575546 is a reply to message #575544] Wed, 23 January 2013 23:58 Go to previous messageGo to next message
sss111ind
Messages: 428
Registered: April 2012
Location: India
Senior Member

WITH TEMP AS
  (SELECT TO_DATE('01-01-2013 00:00:00','dd-mm-yyyy hh24:mi:ss') DATE1,
    TO_DATE('02-01-2013 00:00:00','dd-mm-yyyy hh24:mi:ss') DATE2
  FROM DUAL
  )
SELECT date2-date1 days,(date2-date1)*24 hours,(date2-date1)*24*60 minutes,(date2-date1)*24*60*60 seconds
FROM temp;

WITH TEMP AS
  (SELECT TO_DATE('01-01-2013 00:00:00','dd-mm-yyyy hh24:mi:ss') DATE1,
    TO_DATE('02-01-2013 06:35:40','dd-mm-yyyy hh24:mi:ss') DATE2
  FROM DUAL
  )
SELECT round(DATE2-DATE1) DAYS,round((DATE2-DATE1)*24) HOURS,round((DATE2-DATE1)*24*60) MINUTES,round((DATE2-DATE1)*24*60*60) SECONDS
FROM temp;



Regards,
Nathan
Re: difference between dates [message #575553 is a reply to message #575543] Thu, 24 January 2013 01:35 Go to previous message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i have doubt here how is sysdate calculating the time here


It just asks the database server OS.
Every function is described in Database SQL Reference and the first line for SYSDATE is:

Quote:
SYSDATE returns the current date and time set for the operating system on which the database resides


Regards
Michel
Previous Topic: complex query
Next Topic: taking latest current balance using analytical Query
Goto Forum:
  


Current Time: Sat Apr 19 04:43:14 CDT 2014

Total time taken to generate the page: 0.04570 seconds