Home » SQL & PL/SQL » SQL & PL/SQL » How to get date in words like July 9, 2003 using to_date function
How to get date in words like July 9, 2003 using to_date function [message #200083] Fri, 27 October 2006 15:42 Go to next message
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
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 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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;
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 Go to previous messageGo to next message
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


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 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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!
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 Go to previous messageGo to next message
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
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 Go to previous message
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 !!!
Previous Topic: urgent again.... please help..
Next Topic: Datetime format
Goto Forum:
  


Current Time: Sat Dec 03 12:08:00 CST 2016

Total time taken to generate the page: 0.04586 seconds