Solved

To display results in date range

Posted on 2011-03-18
5
429 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
  • 3
5 Comments
 
LVL 34

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 34

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now