ts84zs
asked on
find difference in dates in oracle sql
i have a table in oracle 11
I am running this sql to find if a task was created 4 or more days earlier than today's date..
select * from mytasks a where TRUNC(sysdate) - TRUNC(a.task_created_date)
Is that sql ok to use to find tasks created 4 days earlier than today's date... Or do I have to set any oracle-environment-variabl
Will the subtraction - "TRUNC(sysdate) - TRUNC(a.task_created_date)
I am running this sql to find if a task was created 4 or more days earlier than today's date..
select * from mytasks a where TRUNC(sysdate) - TRUNC(a.task_created_date)
Is that sql ok to use to find tasks created 4 days earlier than today's date... Or do I have to set any oracle-environment-variabl
Will the subtraction - "TRUNC(sysdate) - TRUNC(a.task_created_date)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As a note for clarification, I know that dates aren't stored as integers - I did state that that it was simplifying the structure - but from understanding how it works a span of 24 hours is represented by the number 1 - which is simply put, an integer.
You're not going store Jan 1, 4712 BCE through Dec 12, 9999 as a basic integer.
Sorry if that caused any confusion to the OP
You're not going store Jan 1, 4712 BCE through Dec 12, 9999 as a basic integer.
Sorry if that caused any confusion to the OP
The result of a subtraction between 2 dates results in a number of days being returned. It is not an integer, it is a number (with decimal places) that gives the difference between the 2 dates represented by the number of days.
Not knowing the actual datatype involved in this scenario, if we are talking about a timestamp, rather than a date, the result of that difference is an interval not a number. If the columns are timestamps, then there is a lot of type conversion happening in the query that is being masked and some of the solutions may not work.
PS. We are all the victim of missing an AND/OR or sign flipping all the time.
Not knowing the actual datatype involved in this scenario, if we are talking about a timestamp, rather than a date, the result of that difference is an interval not a number. If the columns are timestamps, then there is a lot of type conversion happening in the query that is being masked and some of the solutions may not work.
PS. We are all the victim of missing an AND/OR or sign flipping all the time.
Basically, try to avoid apply SQL operators (including: trunc, to_char, to_date, upper, lower, etc.) on any columns that you reference in "where" clauses of queries. Why? These can add a huge performance penalty there. It is OK to use these in the "select..." part of the query and on bind variables in the "where" clauses, because in either of those places the performance impact is minimal.
And yes, when you subtract one Oracle "date" value (or timestamp) from another, the results will be expressed as a number representing the number of days (or the portion of a day or days) between those two "date" values.
And yes, when you subtract one Oracle "date" value (or timestamp) from another, the results will be expressed as a number representing the number of days (or the portion of a day or days) between those two "date" values.
When subtracting timestamps you do not get a number that is the number of days between the 2 timestamps. You get an interval returned.
ASKER
Thanks so much for all the tips
yes, when "flipping" the predicate less than should have been used, sorry
adjusting to - 3 also appears to have corrected another error of mine.
not my finest hour it seems.