Home » SQL & PL/SQL » SQL & PL/SQL » Need help in date functions (Oracle 10.2.0)
Need help in date functions [message #384979] Fri, 06 February 2009 00:02 Go to next message
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 #384981 is a reply to message #384979] Fri, 06 February 2009 00:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Use RR instead of YY in your to_date.
Check the online documentation at http://tahiti.oracle.com for FORMAT MASK
Re: Need help in date functions [message #384982 is a reply to message #384979] Fri, 06 February 2009 00:06 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Try with 'RRMMDD'

Thanks
Trivendra

Sorry Frank: It missed you answer.

[Updated on: Fri, 06 February 2009 00:07]

Report message to a moderator

Re: Need help in date functions [message #384984 is a reply to message #384982] Fri, 06 February 2009 00:08 Go to previous messageGo to next message
shoowaruno
Messages: 14
Registered: February 2009
Junior Member
RRMMDD?

Ok I got it. My mistake. Im changin it inside to_char function.

How bout sorting the date?

[Updated on: Fri, 06 February 2009 00:10]

Report message to a moderator

Re: Need help in date functions [message #384985 is a reply to message #384984] Fri, 06 February 2009 00:10 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
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 #384986 is a reply to message #384985] Fri, 06 February 2009 00:14 Go to previous messageGo to next message
shoowaruno
Messages: 14
Registered: February 2009
Junior Member
How bout sorting the date?

Thanks.
Re: Need help in date functions [message #384987 is a reply to message #384984] Fri, 06 February 2009 00:14 Go to previous messageGo to next message
Frank
Messages: 7880
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 #384989 is a reply to message #384987] Fri, 06 February 2009 00:17 Go to previous messageGo to next message
shoowaruno
Messages: 14
Registered: February 2009
Junior Member
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!
Re: Need help in date functions [message #384990 is a reply to message #384989] Fri, 06 February 2009 00:22 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Take you query to Inline view and order by Check_date desc.
Re: Need help in date functions [message #384991 is a reply to message #384989] Fri, 06 February 2009 00:26 Go to previous messageGo to next message
Frank
Messages: 7880
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 #384994 is a reply to message #384991] Fri, 06 February 2009 00:33 Go to previous messageGo to next message
shoowaruno
Messages: 14
Registered: February 2009
Junior Member
I want it all descending
Re: Need help in date functions [message #384995 is a reply to message #384979] Fri, 06 February 2009 00:38 Go to previous messageGo to next message
shoowaruno
Messages: 14
Registered: February 2009
Junior Member
How will i come up with this output?

Check Date
-----------
Aug-13-2008
Aug-08-2005
Oct-28-2002
May-30-2000
Dec-16-1997

or this output only?

Check Date
-----------
Aug-13-2008

thanks.
Re: Need help in date functions [message #384996 is a reply to message #384994] Fri, 06 February 2009 00:38 Go to previous messageGo to next message
Frank
Messages: 7880
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
icon14.gif  Re: Need help in date functions [message #385000 is a reply to message #384996] Fri, 06 February 2009 00:53 Go to previous messageGo to next message
shoowaruno
Messages: 14
Registered: February 2009
Junior Member
Thanks!
Re: Need help in date functions [message #385005 is a reply to message #384979] Fri, 06 February 2009 01:50 Go to previous messageGo to next message
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;
Re: Need help in date functions [message #385011 is a reply to message #385005] Fri, 06 February 2009 02:49 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What exactly did this add to previous replies?
Previous Topic: decode functioanlty
Next Topic: Update same record simultaneously from two calls to same procedure (merged)
Goto Forum:
  


Current Time: Fri Dec 02 20:27:40 CST 2016

Total time taken to generate the page: 0.22566 seconds