Home » SQL & PL/SQL » SQL & PL/SQL » Date in Words
Date in Words [message #612319] Wed, 16 April 2014 01:01 Go to next message
vipul.gupta2
Messages: 6
Registered: April 2014
Location: India
Junior Member
I need date in words.
Example 2014-02-26(YYYY-MM-DD) the desired output is "Twenty Six February Two Thousand Fourteen"
Please help....
Re: Date in Words [message #612322 is a reply to message #612319] Wed, 16 April 2014 01:12 Go to previous messageGo to next message
Littlefoot
Messages: 19770
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here you go:
SQL> WITH test AS (SELECT DATE '2014-02-26' some_date FROM DUAL),
  2       split
  3          AS (SELECT TO_CHAR (some_date, 'fmdd') t_day,
  4                     TO_CHAR (some_date, 'month') t_month,
  5                     TO_CHAR (some_date, 'yyyy') t_year
  6                FROM test)
  7  SELECT    TO_CHAR (TO_DATE (t_day, 'j'), 'jsp')
  8         || ' '
  9         || t_month
 10         || ' '
 11         || TO_CHAR (TO_DATE (t_year, 'j'), 'jsp')
 12            result
 13    FROM split;

RESULT
------------------------------------------
twenty-six february  two thousand fourteen

SQL>
Re: Date in Words [message #612324 is a reply to message #612319] Wed, 16 April 2014 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59750
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> WITH test AS (SELECT DATE '2014-02-26' some_date FROM DUAL)
  2  select to_char(some_date,'fmddsp month yyyysp') result
  3  from test
  4  /
RESULT
-----------------------------------------
twenty-six february two thousand fourteen


Re: Date in Words [message #612326 is a reply to message #612319] Wed, 16 April 2014 01:31 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2514
Registered: May 2013
Location: World Wide on the Web
Senior Member
vipul.gupta2 wrote on Wed, 16 April 2014 11:31
the desired output is "Twenty Six February Two Thousand Fourteen"


SQL> select  initcap(replace(to_char(to_date('2014-02-26','yyyy-mm-dd'),'fmddsp month yyyysp'),'-',' ')) dt
  2    from dual
  3  /
DT
-----------------------------------------
Twenty Six February Two Thousand Fourteen
Re: Date in Words [message #612327 is a reply to message #612326] Wed, 16 April 2014 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 59750
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Are you sure OP was not able to find the final solution from the example I posted?
Maybe just a link on INITCAP which is not so well-known would be sufficient.

Re: Date in Words [message #612331 is a reply to message #612327] Wed, 16 April 2014 02:09 Go to previous message
vipul.gupta2
Messages: 6
Registered: April 2014
Location: India
Junior Member
thanks all for your answers..!! It's really helpfull.
Previous Topic: Help with PL Packages
Next Topic: query of status changed
Goto Forum:
  


Current Time: Sun Nov 23 10:17:40 CST 2014

Total time taken to generate the page: 0.09265 seconds