... TO_CHAR(TO_DATE(SYSDATE,'DD/MON/YY hh24:mi:ss'),'DD/MM/YY')-
TO_CHAR(TO_DATE(b.c_attribute8,'YYYY/MM/DD hh24:mi:ss'),'DD/MM/YY')Days
....
can you please explain to me the thought process that brings you to a point that says "we should convert dates, which support date arithmetic, into strings to substract them"
why would you try to substract STRINGS
the default, implied conversion for 'string' - 'string' would be
to_number( 'string' ) - to_number( 'string' );
ops$tkyte%ORA11GR1> select *
2 from dual
3 where TO_CHAR(TO_DATE(SYSDATE,'DD/MON/YY hh24:mi:ss'),'DD/MM/YY')-TO_CHAR(TO_DATE(SYSDATE,'DD/MON/YY hh24:mi:ss'),'DD/MM/YY') = 0
4 /
Execution Plan
----------------------------------------------------------
Plan hash value: 3752461848
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(SYSDATE@!),'DD/MON/YY
hh24:mi:ss'),'DD/MM/YY'))-TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(SYSDA
TE@!),'
DD/MON/YY hh24:mi:ss'),'DD/MM/YY'))=0)
to get the difference in days between two dates, all one does is SUBTRACT
sysdate - b.c_attribute8
that is it, just subtract.
you do not know how much this scares me:
AND TO_CHAR(TO_DATE(b.c_attribute8,'YYYY/MM/DD hh24:mi:ss'),'DD/MON/YY')<=
TO_DATE(TO_DATE(SYSDATE,'DD/MON/YY hh24:mi:ss'),'DD/MON/YY')
do you even understand what that does??????
Let us look at just:
TO_DATE(TO_DATE(SYSDATE,'DD/MON/YY hh24:mi:ss'),'DD/MON/YY')
first
that is really:
TO_DATE(
to_char(TO_DATE(
to_char(SYSDATE
),'DD/MON/YY hh24:mi:ss')
),'DD/MON/YY')
the only way to "to_date" something is to have a string. sysdate is a date, not a string, so we have to implicitly convert it to a string using the default date format.
then you convert back into a date using 'dd/mon/yyyy hh24:mi:ss'
well, your default date format is probably what mine is - dd-mon-yyyy, so the time component is GONE, wiped out.
Then, then you TO_DATE the DATE again - that is, you convert the date you just "broke" since you took a date, put it into a string, put it into a date - all with different formats and you convert it
BACK INTO A STRING
to convert it into a date - using the default format this time.
man, there is so much wrong there.
And then, you compare that date to...................
a string:
TO_CHAR(TO_DATE(b.c_attribute8,'YYYY/MM/DD hh24:mi:ss'),'DD/MON/YY')
come on.
dates are comparable. Please, for the love of whatever, just code:
where b.c_attribute8 <= sysdate
or if you want everything based on just the yyyy-mm-dd component (not time) then
where b.c_attribute8 < trunc(sysdate,'d')+1
but stop it, just STOP it with the to_char and to_dates! Pretend that dates are numbers in your head
you can subtract two numbers - you can subtract two dates
you can compare two numbers for equality, greater than, less then - same with dates
true, you cannot add, multiply, or divide two dates - but to that I say "so what, under what possible set of circumstances could you see yourself trying to do so"
so, just pretend they are numbers - and stop it with to_char/to_date.