OraFAQ Forum: SQL & PL/SQL » How to get date in words like July 9, 2003 using to

Home » SQL & PL/SQL » SQL & PL/SQL » How to get date in words like July 9, 2003 using to_date function

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

How to get date in words like July 9, 2003 using to_date function [message #200083]

Fri, 27 October 2006 15:42

miroconnect@yahoo.com
Messages: 202
Registered: April 2006

Senior Member

I want my sql query to return date in words like July 9, 2003

here is my sql

select to_date('2003/07/09', 'yyyy/mm/dd') from dual.Help Please!.
Regards
Miro

Report message to a moderator

Re: How to get date in words like July 9, 2003 using to_date function [message #200085 is a reply to message #200083]

Fri, 27 October 2006 15:53

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You were quite close - there's another TO_CHAR which will solve the problem:

SELECT TO_CHAR (TO_DATE ('2003/07/09', 'yyyy/mm/dd'), 'month d, yyyy')
  FROM DUAL;

Report message to a moderator

Re: How to get date in words like July 9, 2003 using to_date function [message #200087 is a reply to message #200085]

Fri, 27 October 2006 16:13

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Littlefoot wrote on Fri, 27 October 2006 15:53

SELECT TO_CHAR (TO_DATE ('2003/07/09', 'yyyy/mm/dd'), 'month d, yyyy')
  FROM DUAL;

Littlefoot, looks like a 'd' is missing.

SQL> SELECT TO_CHAR (TO_DATE ('2003/07/09', 'yyyy/mm/dd'), 'month d, yyyy') "Single d",
  2           TO_CHAR (TO_DATE ('2003/07/09', 'yyyy/mm/dd'), 'month dd, yyyy') "Two d's"
  3          FROM DUAL;

Single d          Two d's
----------------- ------------------
july      4, 2003 july      09, 2003

If the OP wants to avoid the padding:

SQL> select to_char(to_date('2003/07/09', 'rrrr/mm/dd'),'fmMonth dd, rrrr') 
  2   from dual;

TO_CHAR(TO_DATE('2
------------------
July 9, 2003

Report message to a moderator

Re: How to get date in words like July 9, 2003 using to_date function [message #200088 is a reply to message #200087]

Fri, 27 October 2006 16:19

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I put one 'd' deliberately (to avoid leading zero - not 09, but 9). True, I missed 'fm' format mask. Thank you for correcting me!

Report message to a moderator

Re: How to get date in words like July 9, 2003 using to_date function [message #200314 is a reply to message #200083]

Mon, 30 October 2006 05:57

ajaybabu.yaleti
Messages: 11
Registered: October 2006

Junior Member

Hi,

try this

Eg
select to_char(to_date('01-jan-06'),'month dd, yyyy') from dual

without using to_date function

select to_char(sysdate,'month dd, yyyy') from dual

Regards,
Ajay

Report message to a moderator

Re: How to get date in words like July 9, 2003 using to_date function [message #200317 is a reply to message #200314]

Mon, 30 October 2006 06:01

ebrian
Messages: 2794
Registered: April 2006

Senior Member

ajaybabu.yaleti wrote on Mon, 30 October 2006 05:57

Hi,

try this

Eg
select to_char(to_date('01-jan-06'),'month dd, yyyy') from dual

without using to_date function

select to_char(sysdate,'month dd, yyyy') from dual

Regards,
Ajay

Apparently you aren't even reading the replies to the question !!!

Report message to a moderator

Previous Topic:	urgent again.... please help..
Next Topic:	Datetime format

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 10:24:01 CDT 2024