OraFAQ Forum: SQL & PL/SQL » TRUNC VS TO

Home » SQL & PL/SQL » SQL & PL/SQL » TRUNC VS TO_CHAR

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

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.

Report message to a moderator

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'))

Report message to a moderator

Re: TRUNC VS TO_CHAR [message #245151 is a reply to message #245146]

Fri, 15 June 2007 05:49

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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:

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 28 09:59:27 CDT 2025