TRUNC VS TO_CHAR [message #245146] |
Fri, 15 June 2007 05:25 |
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 |
ThomasG
Messages: 3212 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 |
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 #245154 is a reply to message #245151] |
Fri, 15 June 2007 05:53 |
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
|
|
|