| Greatest Date [message #416462] |
Mon, 03 August 2009 00:37  |
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
|
Member |
|
|
Dear All,
Please help how can find out the greatest date among the column with null.
INPUT
Name Last_A_Usged Last_B_Used May Jun
Abc 5/10/09 5/11/09 5/10/08 5/08/09
bbc 5/10/09 5/11/09 5/08/09
cbc 5/10/09 5/11/09 5/12/09
OUTPUT
Name Last_Used
Abc 5/11/09
bbc 5/11/09
cbc 5/12/09
Please help
Regards,
Arvind kumar
[EDITED by LF: applied [pre] tags]
[Updated on: Mon, 03 August 2009 00:43] by Moderator Report message to a moderator |
|
|
|
| Re: Greatest Date [message #416465 is a reply to message #416462] |
Mon, 03 August 2009 00:42  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Combine GREATEST with NVL.
[EDIT] Forgot to ask ... what does "5/10/09" represent? Is it
5th of October 2009
5th of September 2010
10th of May 2009
9th of May 2010
...
[Updated on: Mon, 03 August 2009 00:48] Report message to a moderator |
|
|
|
| Re: Greatest Date [message #416477 is a reply to message #416465] |
Mon, 03 August 2009 01:21 |
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
|
Member |
|
|
| Littlefoot wrote on Mon, 03 August 2009 11:12 | Combine GREATEST with NVL.
[EDIT] Forgot to ask ... what does "5/10/09" represent? Is it
5th of October 2009
5th of September 2010
10th of May 2009
9th of May 2010
...
|
Thanks but i have tried through this bit not able to find out exact out put can u provide the query so that it's helpfull for us.
Arvind
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Greatest Date [message #416500 is a reply to message #416498] |
Mon, 03 August 2009 02:21 |
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
|
Member |
|
|
Date stored in database as per below format.
Name Last_A_Usged Last_B_Used May Jun
Abc 5/10/09 5/11/09 5/10/08 5/08/09
bbc 5/10/09 5/11/09 5/08/09
cbc 5/10/09 5/11/09 5/12/09
|
|
|
|
| Re: Greatest Date [message #416501 is a reply to message #416500] |
Mon, 03 August 2009 02:23 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
|
But what does that format mean? If you don't know what the format means, do to the person who designed the database and ask them.
|
|
|
|
|
|
| Re: Greatest Date [message #416504 is a reply to message #416503] |
Mon, 03 August 2009 02:34 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
| kr_arvind wrote on Mon, 03 August 2009 09:31 | Date format is dd/mm/yyyy
|
No, it isn't. If the date format is dd/mm/yyyy, then that date would be the fifth of August in the year nine.
[Updated on: Mon, 03 August 2009 02:35] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Greatest Date [message #416517 is a reply to message #416513] |
Mon, 03 August 2009 03:21 |
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
|
Member |
|
|
CREATE TABLE TEST2
(
Name VARCHAR2(12 BYTE),
A_LAST_USED DATE,
B_LAST_USED DATE,
MAY09 DATE,
JUN09 DATE
)
INSERT INTO TEST2 ( NAME, A_LAST_USED, B_LAST_USED, MAY09, JUN09 ) VALUES ('A', TO_Date( '07/31/2009 12:11:09 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '07/31/2009 11:57:11 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '05/31/2009 09:00:08 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '06/30/2009 03:07:33 PM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEST2 ( NAME, A_LAST_USED, B_LAST_USED, MAY09, JUN09 ) VALUES ( 'B', NULL, NULL, TO_Date( '05/25/2009 10:48:36 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL);
OUTPUT
Name LastUsed
A 31/07/2009 12:11:09
B 25/05/2009 10:48:36
|
|
|
|
|
|
| Re: Greatest Date [message #416521 is a reply to message #416519] |
Mon, 03 August 2009 03:51 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
SQL> SELECT name,
2 Greatest(Nvl(A_LAST_USED,
3 To_Date('01.01.0001','dd.mm.yyyy')),
4 Nvl(B_LAST_USED,
5 To_Date('01.01.0001','dd.mm.yyyy')),
6 Nvl(MAY09 ,
7 To_Date('01.01.0001','dd.mm.yyyy')),
8 Nvl(JUN09 ,
9 To_Date('01.01.0001','dd.mm.yyyy'))
10 ) LAST_USED
11 FROM TEST2;
NAME LAST_USED
------------ -------------------
A 31.07.2009 12:11:09
B 25.05.2009 10:48:36
SQL>
Now please go and read the forum guide on how to format your posts.
|
|
|
|
|
|
| Re: Greatest Date [message #416539 is a reply to message #416527] |
Mon, 03 August 2009 05:09 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Yeah, I was kinda torn on this one.
But since he managed what a lot of people didn't manage lately, namely posting a working test case (albeit unformatted) I gave him a last chance.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
