Home » SQL & PL/SQL » SQL & PL/SQL » TRUNC VS TO_CHAR
TRUNC VS TO_CHAR [message #245146] Fri, 15 June 2007 05:25 Go to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

I just feel wonder why When I use TO_NUMBER(TRUNC(l_date_of_claim,'YYYY')), it has error, it is correct when I use TO_NUMBER(TO_CHAR(TRUNC(l_date_of_claim,'YYYY'))) - why I must convert it into CHAR then only allow to convert it into number?

Pls advise....

Ying
Re: TRUNC VS TO_CHAR [message #245147 is a reply to message #245146] Fri, 15 June 2007 05:33 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Becauste there is no to_number([date]) command at all in oracle, but there is a to_number([char]) command.

Re: TRUNC VS TO_CHAR [message #245150 is a reply to message #245146] Fri, 15 June 2007 05:40 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
TRUNC(DATE) does not return the 'YYYY' portion of a date as a string. It truncates the date to the start of the year, so you get e.g. 01-jan-07. But this is still a DATE, so it is meaningless to apply TO_NUMBER to it.

I think what you are really trying to do is:

TO_NUMBER(TO_CHAR(l_date_of_claim,'YYYY'))
Re: TRUNC VS TO_CHAR [message #245151 is a reply to message #245146] Fri, 15 June 2007 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TRUNC(l_date_of_claim,'YYYY') returns a date so TO_NUMBER of this is an error.
TO_CHAR(TRUNC(l_date_of_claim,'YYYY')) is used to format a full date and does not return the same thing (that is only the year).
To only get the year you have to use TO_CHAR(...,'YEAR') but this is not to work on it as a number even if this can be done using the to_number function.
If you want to play with year as a number use EXTRACT(YEAR from mydate).

Regards
Michel
Re: TRUNC VS TO_CHAR [message #245154 is a reply to message #245151] Fri, 15 June 2007 05:53 Go to previous message
ying
Messages: 143
Registered: May 2002
Senior Member
Dear All,

Thanks so much for the answer, I know why already, when I see the output, only I realise that what Cthulhu said is correct:

Quote:
TRUNC(DATE) does not return the 'YYYY' portion of a date as a string. It truncates the date to the start of the year, so you get e.g. 01-jan-07. But this is still a DATE, so it is meaningless to apply TO_NUMBER to it.


Thanks!

[Updated on: Fri, 15 June 2007 05:53] by Moderator

Report message to a moderator

Previous Topic: How this function works??
Next Topic: Audit Trail
Goto Forum:
  


Current Time: Sun Dec 04 10:16:48 CST 2016

Total time taken to generate the page: 0.24072 seconds