Home » SQL & PL/SQL » SQL & PL/SQL » Can Any one please tell me how this query working???
Can Any one please tell me how this query working??? [message #584999] Tue, 21 May 2013 03:54 Go to next message
mdsirajoddin
Messages: 3
Registered: July 2011
Location: Hyderabad
Junior Member


SELECT REPLACE(REPLACE(TO_CHAR(TO_DATE(150,'J'),'Jsp'),'-',' '),'Hundred','Hundred and')FROM DUAL ;
Re: Can Any one please tell me how this query working??? [message #585001 is a reply to message #584999] Tue, 21 May 2013 04:17 Go to previous messageGo to next message
Littlefoot
Messages: 19321
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Step by step.

TO_DATE with 'J' format mask returns a Julian date (number of days since January 1 4712 BC):
SQL> select to_date(251, 'j') from dual;

TO_DATE(251,'J')
-------------------
08.09.4712 00:00:00

SQL>


Now, apply TO_CHAR with 'JSP' format mask (which takes that Julian date and spells it):
SQL> select to_char(to_date(251, 'j'), 'jsp') result from dual;

RESULT
---------------------
two hundred fifty-one

SQL>


REPLACE functions in your code
a) remove the hyphen (-)
b) change "Hundred" to "Hundred and"
Re: Can Any one please tell me how this query working??? [message #585006 is a reply to message #584999] Tue, 21 May 2013 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 58607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
All format masks are described in Database SQL Reference, section Datetime Format Models

Regards
Michel
Re: Can Any one please tell me how this query working??? [message #585083 is a reply to message #585001] Tue, 21 May 2013 23:07 Go to previous messageGo to next message
mdsirajoddin
Messages: 3
Registered: July 2011
Location: Hyderabad
Junior Member

Small Doubt bro,

Step by step.

TO_DATE with 'J' format mask returns a Julian date (number of days since January 1 4712 BC):

SQL> select to_date(251, 'j') from dual;

TO_DATE(251,'J')
-------------------
08.09.4712 00:00:00 -- This is the Answer I USED IN TO_CHAR()

How its spelling the number when used the To_Char( 08.09.4712 00:00:00,'jsp');

Re: Can Any one please tell me how this query working??? [message #585084 is a reply to message #585083] Tue, 21 May 2013 23:43 Go to previous messageGo to next message
BlackSwan
Messages: 22520
Registered: January 2009
Senior Member
>How its spelling the number when used the To_Char( 08.09.4712 00:00:00,'jsp');

NEVER use TO_CHAR on a string.
In Oracle strings are delimited by single quote marks.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Can Any one please tell me how this query working??? [message #585088 is a reply to message #585083] Wed, 22 May 2013 00:16 Go to previous messageGo to next message
Michel Cadot
Messages: 58607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How its spelling the number when used the To_Char( 08.09.4712 00:00:00,'jsp');


You missed the sign on the year and how it does is internal algorithm.
if you post the actual problem you are trying to solve maybe we can help you in better way than explaining why/how you don't understand something.

Regards
Michel
Re: Can Any one please tell me how this query working??? [message #585093 is a reply to message #585088] Wed, 22 May 2013 00:49 Go to previous message
Littlefoot
Messages: 19321
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Read Ask Tom's "Spell the number"; you might find it interesting.
Previous Topic: lock table (2 Merged)
Next Topic: Display data in Chinese language in window 7
Goto Forum:
  


Current Time: Tue Jul 29 04:09:01 CDT 2014

Total time taken to generate the page: 0.08898 seconds