Home » SQL & PL/SQL » SQL & PL/SQL » regarding dates
regarding dates [message #263042] Tue, 28 August 2007 22:24 Go to next message
msafana
Messages: 31
Registered: July 2007
Member
hi all,
iam safana.i have one doubt.
if i assign 2 dates to 2 variables and then i want to know which is greater between them.may i know the query for this.


Thank u.
safana
Re: regarding dates [message #263044 is a reply to message #263042] Tue, 28 August 2007 22:26 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Just minus.

date1-date2
Re: regarding dates [message #263048 is a reply to message #263042] Tue, 28 August 2007 22:49 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
decode(sign(date1-date2),1,date1,date2) greater_date
Re: regarding dates [message #263064 is a reply to message #263042] Wed, 29 August 2007 00:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
case when date1 >= date2 then date1 else date2 end

Regards
Michel
Re: regarding dates [message #263065 is a reply to message #263042] Wed, 29 August 2007 00:26 Go to previous messageGo to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

SQL> SELECT GREATEST('01-JAN-2007','29-AUG-2007') FROM DUAL;

GREATEST('0
-----------
29-AUG-2007

Regards,
Thani........
Re: regarding dates [message #263093 is a reply to message #263065] Wed, 29 August 2007 01:14 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
thani_oracle wrote on Wed, 29 August 2007 07:26
SQL> SELECT GREATEST('01-JAN-2007','29-AUG-2007') FROM DUAL;


SQL> SELECT GREATEST('29-JAN-2007','01-AUG-2007') FROM DUAL;

GREATEST('2
-----------
29-JAN-2007
Thani, you probably know what answer you'd get from 99% of this forum, right? We're trying to compare dates, not strings. Very Happy

MHE
Re: regarding dates [message #263137 is a reply to message #263093] Wed, 29 August 2007 03:21 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
However,

GREATEST(date1, date2)

would be my first choice, e.g.

GREATEST(DATE '2007-01-30', DATE '2007-08-01')
Re: regarding dates [message #263153 is a reply to message #263093] Wed, 29 August 2007 04:10 Go to previous messageGo to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Thank you....

But William Robertson has given good solution for using greatest function in date

Thanks and Regards,
Thani....
Re: regarding dates [message #263209 is a reply to message #263153] Wed, 29 August 2007 07:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Thani,

What Maaher is pointing out is that while everyone else has given a solution for comparing two dates, you have given a solution for comparing two strings.

'01-JAN-2007' and '29-AUG-2007' are not dates. They're strings.
Re: regarding dates [message #263231 is a reply to message #263209] Wed, 29 August 2007 08:14 Go to previous messageGo to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Thanks ... sorry
Re: regarding dates [message #263593 is a reply to message #263042] Thu, 30 August 2007 08:24 Go to previous messageGo to next message
lenin_babu55
Messages: 12
Registered: August 2007
Junior Member
DECLARE
D1 DATE:='&D1';
D2 DATE:='&D2';
BEGIN
IF D1<D2 THEN
DBMS_OUTPUT.PUT_LINE('D1 IS GREATER');
ELSE
DBMS_OUTPUT.PUT_LINE('D2 IS GREATER');
END IF;
END;
Re: regarding dates [message #263616 is a reply to message #263593] Thu, 30 August 2007 09:11 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Unless they are the same (or one or both are null, though that goes for all the above solutions).
Re: regarding dates [message #263671 is a reply to message #263616] Thu, 30 August 2007 12:56 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Heh, heh, just noticed that lenin_babu's answer might give correct results in the Bizarro world, but not here.
Re: regarding dates [message #264649 is a reply to message #263042] Tue, 04 September 2007 02:09 Go to previous message
msafana
Messages: 31
Registered: July 2007
Member
thanks for all who has given me the solution.
Previous Topic: View query
Next Topic: How many Primary keys can a table have???
Goto Forum:
  


Current Time: Sat Dec 03 01:15:17 CST 2016

Total time taken to generate the page: 0.11319 seconds