Home » SQL & PL/SQL » SQL & PL/SQL » Using DECODE to determine if a date falls within a date range (Oracle database 11g SQLPlus)
Using DECODE to determine if a date falls within a date range [message #334931] Fri, 18 July 2008 13:26 Go to next message
terrym0113
Messages: 2
Registered: July 2008
Junior Member
I'm new to using SQL Plus and need help with DECODE or some other function that might work better. What I'm trying to do is determine if a date falls within a certain date range, if it does, I want to populate my field with a 'Yes' and a 'No' if it does not. In the code below I'm trying to determine a number, if the number is less than zero (-0.0) the date falls before my date range, if the number is 0 to 1, the date falls within the range, if greater than 1 the date falls after my date range.

Here is my code:

DECODE(((e.EFFECTIVE_END_DATE-'&sdate')/('&edate'-'&sdate')),0,'No',1,'Yes','No') status_change

Here is the resulting error message:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE

Other info:
sdate = Start date of range
edate = End date of range

Thanks in advance for your assistance.
Re: Using DECODE to determine if a date falls within a date range [message #334932 is a reply to message #334931] Fri, 18 July 2008 13:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use CASE.

Regards
Michel
Re: Using DECODE to determine if a date falls within a date range [message #334945 is a reply to message #334932] Fri, 18 July 2008 15:19 Go to previous messageGo to next message
terrym0113
Messages: 2
Registered: July 2008
Junior Member
Thanks. Worked like a champ!

Here is my final code just in case someone else following this thread may find it a useful example:

(CASE WHEN e.EFFECTIVE_END_DATE BETWEEN '&sdate' AND '&edate' THEN 'Yes' ELSE 'No' END) status_change
Re: Using DECODE to determine if a date falls within a date range [message #334955 is a reply to message #334945] Fri, 18 July 2008 17:59 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Hi Terry, really bad idea to compare dates and strings. look into the to_date function (actually, also a bad idea to use substitution variables instead of bind variables too)

[Updated on: Fri, 18 July 2008 18:01]

Report message to a moderator

Previous Topic: Index creation error
Next Topic: SQL Question
Goto Forum:
  


Current Time: Wed Dec 07 04:31:28 CST 2016

Total time taken to generate the page: 0.10391 seconds