syhctl
asked on
SQL/Pl - Date Calculation: Number of days between two dates
Hello,
Could you please give me the proper syntax to calculate the # of days betwen dates
both fields are date types and I need a number result.
Date1 - Date2 = #of Days
Also, if the resulting number is (-) how do I assure all #'s will be displayed as (+)
Thanks much as always,
Syhctl
Could you please give me the proper syntax to calculate the # of days betwen dates
both fields are date types and I need a number result.
Date1 - Date2 = #of Days
Also, if the resulting number is (-) how do I assure all #'s will be displayed as (+)
Thanks much as always,
Syhctl
ASKER
That didn't seem to work for me, would I perhaps need to use something like
TO_DATE(TO_CHAR(SUBSTR)?
TO_DATE(TO_CHAR(SUBSTR)?
no don't need to do that - subtraction of two dates gives a number as the result - from the docs:
"Date Arithmetic
You can add and subtract number constants as well as other dates from dates. Oracle interprets number constants in arithmetic date expressions as numbers of days. For example, SYSDATE + 1 is tomorrow. SYSDATE - 7 is one week ago. SYSDATE + (10/1440) is ten minutes from now. Subtracting the hiredate column of the sample table employees from SYSDATE returns the number of days since each employee was hired. You cannot multiply or divide DATE values."
"Because each date contains a time component, most results of date operations include a fraction. This fraction means a portion of one day. For example, 1.5 days is 36 hours."
What is the result that you are getting?
"Date Arithmetic
You can add and subtract number constants as well as other dates from dates. Oracle interprets number constants in arithmetic date expressions as numbers of days. For example, SYSDATE + 1 is tomorrow. SYSDATE - 7 is one week ago. SYSDATE + (10/1440) is ten minutes from now. Subtracting the hiredate column of the sample table employees from SYSDATE returns the number of days since each employee was hired. You cannot multiply or divide DATE values."
"Because each date contains a time component, most results of date operations include a fraction. This fraction means a portion of one day. For example, 1.5 days is 36 hours."
What is the result that you are getting?
ASKER
The statement doesn't run, I get this is the error message I get:
SQL error. Stmt #: 5653 Error Position: 1353 Return: 1722 - ORA-01722: invalid number
A SQL error occurred. Please consult your system log for details.
Error in running query because of SQL Error, Code=1722, Message=ORA-01722: invalid number (50,380)
SQL error. Stmt #: 5653 Error Position: 1353 Return: 1722 - ORA-01722: invalid number
A SQL error occurred. Please consult your system log for details.
Error in running query because of SQL Error, Code=1722, Message=ORA-01722: invalid number (50,380)
Can you post the section of code where you are getting this error?
ASKER
In PeopleSoft query, I enter the syntax in expression - use as field and run query. It does not tell me what part of the expression is incorrect.
what is the format of the date you are entering for date1 and date2..can you post that ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so Much - the "abs(To_Date) solution worked..
if you want whole number of days only then
abs(trunc(date1) - trunc(date2))