Solved

To display results in date range

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
report returning null 21 79
Getting a return value from an IN OUT parameter in Oracle? 7 44
join 2 views with 5 conditions 3 44
Oracle sql query 7 43
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…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

911 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

25 Experts available now in Live!

Get 1:1 Help Now