Need help in date functions [message #384979] |
Fri, 06 February 2009 00:02  |
shoowaruno
Messages: 14 Registered: February 2009
|
Junior Member |
|
|
I have this query:
SELECT to_char(to_date(ckdte, 'yymmdd') , 'Mon-dd-yyyy') "Check Date"
from lgloaninfomf
WHERE ssnum =
(SELECT ssnum from RCEESTATICMF WHERE ssnum = 3315899990 AND dobth = 710318);
And this is the output:
Check Date
-----------
May-30-2000
Oct-28-2002
Aug-08-2005
Aug-13-2008
Dec-16-2097
My problem is, how will i change that 2097 to 1997? ..
CKDTE's datatype is in VARCHAR thats why i had to use this to_date function and to_char for the format.
How will i achieve that result? and I need to sort the year in descending order.. but HOW?
please please help me. Thanks so much!
|
|
|
|
|
|
Re: Need help in date functions [message #384985 is a reply to message #384984] |
Fri, 06 February 2009 00:10   |
trivendra
Messages: 211 Registered: October 2007 Location: Phoenix
|
Senior Member |
|
|
With this
SQL> SELECT TO_CHAR (TO_DATE ('971216', 'yymmdd'), 'Mon-dd-yyyy') "Check Date"
FROM DUAL
Check Date
-----------
Dec-16-2097
1 row selected.
SQL> SELECT TO_CHAR (TO_DATE ('971216', 'rrmmdd'), 'Mon-dd-yyyy') "Check Date"
FROM DUAL
Check Date
-----------
Dec-16-1997
1 row selected.
Thanks
Trivendra
|
|
|
|
Re: Need help in date functions [message #384987 is a reply to message #384984] |
Fri, 06 February 2009 00:14   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
shoowaruno wrote on Fri, 06 February 2009 07:08 | RRMMDD?
Ok I got it. My mistake. Im changin it inside to_char function.
How bout sorting the date?
|
Do you want to order the years descending and the dates within the years ascending, or do you want it all descending?
|
|
|
|
|
Re: Need help in date functions [message #384991 is a reply to message #384989] |
Fri, 06 February 2009 00:26   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
shoowaruno wrote on Fri, 06 February 2009 07:17 | I want the years in descending order.
I should have this output:
Check Date
-----------
Aug-13-2008
Aug-08-2005
Oct-28-2002
May-30-2000
Dec-16-1997
thanks!
|
That does not answer my question.
|
|
|
|
|
Re: Need help in date functions [message #384996 is a reply to message #384994] |
Fri, 06 February 2009 00:38   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Then simply order by to_date(<your_string>, <your_format>) desc:
SQL> with dates_as_strings as
2 ( select to_char(hiredate, 'YYMMDD') as hiredate
3 from emp
4 )
5 select to_char(to_date(hiredate, 'RRMMDD'), 'dd fmMonth YYYY')
6 from dates_as_strings
7 order by to_date(hiredate, 'RRMMDD') desc
8 /
TO_CHAR(TO_DATE(H
-----------------
12 January 1983
09 December 1982
23 January 1982
03 December 1981
03 December 1981
17 November 1981
28 September 1981
08 September 1981
09 June 1981
01 May 1981
02 April 1981
22 February 1981
20 February 1981
17 December 1980
|
|
|
|
Re: Need help in date functions [message #385005 is a reply to message #384979] |
Fri, 06 February 2009 01:50   |
rkumar2
Messages: 1 Registered: February 2009
|
Junior Member |
|
|
SELECT to_char(to_date(ckdte, 'rrmmdd') , 'Mon-dd-yyyy') "Check Date"
from lgloaninfomf
WHERE ssnum =
(SELECT ssnum from RCEESTATICMF WHERE ssnum = 3315899990 AND dobth = 710318)
order by to_date(ckdte, 'rrmmdd') desc;
|
|
|
|