OraFAQ Forum: SQL & PL/SQL » conditional date range using CASE statement in WHERE clause

Home » SQL & PL/SQL » SQL & PL/SQL » conditional date range using CASE statement in WHERE clause (Oracle 9.2.0.5.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

conditional date range using CASE statement in WHERE clause [message #501326]

Mon, 28 March 2011 09:41

staann56
Messages: 136
Registered: May 2006
Location: atlanta

Senior Member

Is it possible within a CASE statement to put conditions on the date range that I want to pull? IE: am versus pm. The query has to pull specific time ranges for an AM run versus a PM run.

.....
FROM
   table
WHERE
   CASE 
     WHEN TO_CHAR(SYSDATE,'AM') = 'AM' 
       THEN table.date BETWEEN TRUNC(SYSDATE) AND SYSDATE 
     ELSE table.date BETWEEN TRUNC(SYSDATE+12/24) AND SYSDATE

any suggestions would be greatly appreciated.

Stan

[Updated on: Mon, 28 March 2011 10:25] by Moderator

Report message to a moderator

Re: conditional date range using CASE statement in WHERE clause [message #501327 is a reply to message #501326]

Mon, 28 March 2011 09:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

FROM TABLE
WHERE table.date between TRUNC(SYSDATE) AND SYSDATE

Report message to a moderator

Re: conditional date range using CASE statement in WHERE clause [message #501330 is a reply to message #501326]

Mon, 28 March 2011 10:29

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

WHERE table.date 
        BETWEEN 
      trunc(sysdate)+trunc(to_number(to_char(sysdate,'HH24')/12)/2
        AND
      sysdate

Regards
Michel

Report message to a moderator

Re: conditional date range using CASE statement in WHERE clause [message #501341 is a reply to message #501330]

Mon, 28 March 2011 11:28

staann56
Messages: 136
Registered: May 2006
Location: atlanta

Senior Member

thank you very much for the replies. Unfortunately, neither of these will work. I'm probably not explaining clearly what I'm trying to do. Currently I have an AM and a PM version of a query that I need to consolidate into one query.

The AM version uses these times for data capture:

....
FROM
   table
WHERE 
   table.date > (TRUNC(SYSDATE-1)+(19*60+0)/1440)

and the PM version uses these times for data capture:


> 
....
FROM
   table
WHERE 
   table.date > (TRUNC(SYSDATE)+(7*60+0)/1440)

If possible I need the query when ran in the AM or PM to use the correct time constraint respectively.

So to summarize, if the query is ran during any AM hour it pulls based on the first date constraint listed above else the second date constraint.

Thanks - Stan.

Report message to a moderator

Re: conditional date range using CASE statement in WHERE clause [message #501342 is a reply to message #501341]

Mon, 28 March 2011 11:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you provided Test Case - http://www.orafaq.com/wiki/Test_case
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.

Report message to a moderator

Re: conditional date range using CASE statement in WHERE clause [message #501343 is a reply to message #501341]

Mon, 28 March 2011 11:42

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Unfortunately, neither of these will work.

Actually mine fit EXACTLY what you posted.

Quote:

I'm probably not explaining clearly what I'm trying to do.

This is clear your second post has nothing to do with your first expression.

19-7=12, so you can apply what I said in the same way.

I should be better for your next question you post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data.

Regards
Michel

Report message to a moderator

Re: conditional date range using CASE statement in WHERE clause [message #501344 is a reply to message #501343]

Mon, 28 March 2011 11:51

staann56
Messages: 136
Registered: May 2006
Location: atlanta

Senior Member

Will do.

Thanks guys.

Report message to a moderator

Previous Topic:	Updating List in a file
Next Topic:	PL/SQL native compilation (3 Merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 31 10:44:37 CDT 2025