Solved

To display results in date range

Posted on 2011-03-18
5
433 Views
Last Modified: 2013-12-07
I am using a query where i am trying to get the result where the datecolumn>='Fromdate' and datecolum<=ToDate'.
Ex: SELECT  
        cc.date,
        cc.col2 AS Description
FROM    table1 cc,
        table2 pn
WHERE    cc.date>=(SELECT DISTINCT
            CASE
              WHEN :F1 IS NULL
              THEN sysdate - 60
              ELSE :F1
            END
          FROM dual
          )
        AND cc.date<=
          (SELECT DISTINCT
            CASE
              WHEN :F2 IS NULL
              THEN sysdate
              ELSE :F2
            END
          FROM dual
          )
Here F1 is From Date i.e. '1-MAR-2011' and F2 is ToDate i.e. '17-MAR-2011'
here I am not getting the records which are in the date of 17 th march 2011.
If I change the date to '18-MAR-2011' then I am getting the records on 17th march.

Is there any thing worng with the query?
Expectation is as I am using <='17-MAR-2011', I need to get the result of 17 March also.

0
Comment
Question by:mrkkishore
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 35166500
Your date column contains a time portion which is not equal to midnight.

I believe you should also be able to simplify your query to this:
SELECT  
        cc.date,
        cc.col2 AS Description
FROM    table1 cc,
        table2 pn
WHERE    cc.date>=nvl(to_date(:f1, 'DD-MON-YYYY'), trunc(sysdate) - 60)
        AND cc.date<= nvl(to_date(:f2, 'DD-MON-YYYY'), trunc(sysdate)) + (84399/84440)

Open in new window

0
 
LVL 35

Expert Comment

by:johnsone
ID: 35166512
Actually, I fat fingered the number, it should be this:

SELECT  
        cc.date,
        cc.col2 AS Description
FROM    table1 cc,
        table2 pn
WHERE    cc.date>=nvl(to_date(:f1, 'DD-MON-YYYY'), trunc(sysdate) - 60)
        AND cc.date<= nvl(to_date(:f2, 'DD-MON-YYYY'), trunc(sysdate)) + (86399/86440)

Open in new window

0
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 35166521
OK, not I did it twice.  This is definitely the right number.

86400 is the number of seconds in 1 day.
SELECT  
        cc.date,
        cc.col2 AS Description
FROM    table1 cc,
        table2 pn
WHERE    cc.date>=nvl(to_date(:f1, 'DD-MON-YYYY'), trunc(sysdate) - 60)
        AND cc.date<= nvl(to_date(:f2, 'DD-MON-YYYY'), trunc(sysdate)) + (86399/86400)

Open in new window

0
 

Author Closing Comment

by:mrkkishore
ID: 35166779
Thank you very much,it is working
0
 
LVL 3

Expert Comment

by:pnedic
ID: 35166881
Hello
First :
I am very suspicious that your Select Distinct Case query is correct and will return anything ! On the other hand you have only dummy field in Dual table.
Second:
Generally, instead of using CASE function, you can use much easier NVL function as:
SELECT NVL(F1, sysdate-60) FROM .....
NVL function does following: if F1 is null then return "sysdate-60", otherwise return F1
Third:
Why you use "table2 pn" in FROM part, since you don't have any field selected from it ?

Now, if you use where condition as "cc.date >= '1-Mar-2011' and cc.date <= '17-Mar-2011'" it MUST include both dates (from and to), unless there is nothing on 17 th march.  Try to avoid your CASE statements and "hardcode" your WHERE part (as I written above) and check the query result.
The Oracle has "default" date format (used for where condition) as "d-Mon-yyyy" so you can use this format without explicit conversion (cast). Otherwise you can use this cast form (e.g.):
cc.date >= TO_DATE('01.03.2011', 'DD.MM.YYYY')

Last question (to check it): is the cc.date fiield of DATE type ? If it is not, then you must think about casting from original filed type to DATE type.

Regards
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question