Solved

To display results in date range

Posted on 2011-03-18
5
431 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.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

777 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