Home » SQL & PL/SQL » SQL & PL/SQL » Date comparison using DECODE (SQL)
Date comparison using DECODE [message #404468] Thu, 21 May 2009 21:23 Go to next message
aryansh04
Messages: 3
Registered: March 2008
Junior Member
Hi people,
I have compared 2 date using the below statement using decode. and it worked...

SELECT DECODE( '[REQ.P.DELEGATION_START_DATE]',SYSDATE,'Y','N') FROM DUAL

This is an equality comparision.
Now i want to write query using DECODE which gives me 'Y' if [REQ.P.DELEGATION_START_DATE] <= SYSDATE and 'N' for the other case.

can anyone please help me with this?
I want a single query.

Thanks in advance.
Re: Date comparison using DECODE [message #404470 is a reply to message #404468] Thu, 21 May 2009 21:35 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@aryansh04,

Manipulate SIGN Function.

SQL> SELECT sysdate FROM Dual;

SYSDATE
---------
22-MAY-09

SQL> SELECT SIGN(sysdate - to_date('21-05-2009','DD-MM-YYYY')) sign_fn
  2  FROM DUAL;

   SIGN_FN
----------
         1

SQL> SELECT SIGN(to_date('21-05-2009','DD-MM-YYYY') - sysdate) sign_fn
  2  FROM DUAL;

   SIGN_FN
----------
        -1

SQL> SELECT SIGN(sysdate - sysdate) sign_fn
  2  FROM DUAL;

   SIGN_FN
----------
         0

SQL>


Hope this helps.
Regards,
Jo
Re: Date comparison using DECODE [message #404478 is a reply to message #404468] Thu, 21 May 2009 23:14 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

You do you want to do only DECODE ??

You can even use Case When Statement
Re: Date comparison using DECODE [message #404580 is a reply to message #404478] Fri, 22 May 2009 06:30 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
[nit picking hat on]
ashoka_bl wrote on Fri, 22 May 2009 05:14
You do you want to do only DECODE ??

You can even use Case When Statement

Of course you mean the CASE expression
[/nit picking hat on]

Re: Date comparison using DECODE [message #404581 is a reply to message #404468] Fri, 22 May 2009 06:34 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Ya i meant CASE Expression

Donno what OP has tried so far
Re: Date comparison using DECODE [message #404631 is a reply to message #404468] Fri, 22 May 2009 09:26 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@aryansh04,
aryansh04 PM message on Fri, 22 May 2009 11:23
Hi jo,
I tried below query using your query.

SELECT decode((SIGN(to_date('21-05-2009','DD-MM-YYYY') - sysdate) <= 0 ),Y,N) days FROM DUAL

But it says "missing right parenthesis"

Can u solve this error.


You can't use relational operator in Decode. SIGN Function are manipulated in DECODE Function for that reason. SIGN Function will have three outputs:- 1, 0, -1(Go through the link I have posted. It has loads of examples)

Example to check if 1 is greater or lesser than 2 using DECODE.
SQL> SELECT DECODE(SIGN(1-2), 1, 'Greater','Lesser') DECODE_EX FROM DUAL;

DECODE
------
Lesser


As others mentioned you can also use CASE Statement for this.

Regards,
Jo

[Updated on: Fri, 22 May 2009 15:51]

Report message to a moderator

Previous Topic: ACL parameter problem
Next Topic: diffDiffereence when we use INDEX BY BINARY_INTEGER in TypePE
Goto Forum:
  


Current Time: Sat Dec 03 16:10:56 CST 2016

Total time taken to generate the page: 0.07239 seconds