[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

To display results in date range

Posted on 2011-03-18
5
Medium Priority
?
439 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 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 2000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

864 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