November 18, 2008 at 2:59 am
I am developing a report using BIDS with an Oracle backend.
I am trying to calculate the difference between 2 dates and filter where it's <= 2.
The 2 fields are date fields and i am using:
TO_DATE(TO_CHAR(SYSDATE), 'dd/mm/yyyy') - TO_DATE(TO_CHAR(ITEM_DATA.DATE1), 'dd/mm/yyyy')
This gives me 14, but when i then add a filter <= 2, it will not work. I have tried lots of combinations
ie SYSDATE - ITEM_DATA.DATE1 but keep running into errors.
Can someone put me out of my misery??!!
Thanks, Steve
November 18, 2008 at 11:55 pm
Hi
Your to_char and to_char statements are not correct, it should be like this:
TO_DATE(TO_CHAR(SYSDATE, 'dd/mm/yyyy'),'dd/mm/yyyy') - TO_DATE(TO_CHAR(ITEM_DATA.DATE1, 'dd/mm/yyyy'),'dd/mm/yyyy')
Hope this helps 🙂
November 19, 2008 at 2:39 am
Thanks for the response.
However, the issue is that if I try to apply a filter to this statement, say <= 2, it is automatically changed to <= TO_DATE(2, 'J') and then fails. I've also tried using wrapping TO_NUMBER etc around the expression but none of these functions work.
The issue seems to be converting the expression to a number and then applying a numerical filter to this but I can't seem to find the right combination?
November 19, 2008 at 7:41 am
Try adding substring function to your date conversion before you add the to_number conversion, then perform your numeric operation, and filter on this new field:
to_number(
SUBSTR(TO_CHAR(SYSDATE, 'dd/mm/yyyy'),'dd/mm/yyyy'),1,2)
) -
to_number(
SUBSTR(TO_CHAR(DateField, 'dd/mm/yyyy'),'dd/mm/yyyy'),1,2)
) as Day_Diff
jc
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply