OraFAQ Forum: SQL & PL/SQL » Date comparison using DECODE

Home » SQL & PL/SQL » SQL & PL/SQL » Date comparison using DECODE (SQL)

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

Date comparison using DECODE [message #404468]

Thu, 21 May 2009 21:23

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.

Report message to a moderator

Re: Date comparison using DECODE [message #404470 is a reply to message #404468]

Thu, 21 May 2009 21:35

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

Report message to a moderator

Re: Date comparison using DECODE [message #404478 is a reply to message #404468]

Thu, 21 May 2009 23:14

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

Report message to a moderator

Re: Date comparison using DECODE [message #404580 is a reply to message #404478]

Fri, 22 May 2009 06:30

pablolee
Messages: 2882
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]

Report message to a moderator

Re: Date comparison using DECODE [message #404581 is a reply to message #404468]

Fri, 22 May 2009 06:34

ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore

Senior Member

Ya i meant CASE Expression

Donno what OP has tried so far

Report message to a moderator

Re: Date comparison using DECODE [message #404631 is a reply to message #404468]

Fri, 22 May 2009 09:26

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 11 04:54:15 CDT 2025