OraFAQ Forum: SQL & PL/SQL » To

Home » SQL & PL/SQL » SQL & PL/SQL » To_CHAR Problem

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

To_CHAR Problem [message #206306]

Wed, 29 November 2006 09:45

deep_gh1
Messages: 13
Registered: October 2006
Location: India

Junior Member

Hi,

This is Randeep.As we know TO_CHAR converts date or number to character string, so I tried the query like

SQL> select TO_CHAR(sysdate,'Month') as Now_Month from dual;

and it was working fine.But when I tried sth like below

SQL> select TO_CHAR(29-NOV-06,'Month') as Now_Month from dual;

I got the error below??

ERROR at line 1:
ORA-00904: "NOV": invalid identifier

Then I tried to change the default date format and typed like

SQL> alter session set NLS_DATE_FORMAT = 'DD-MM-YY';

and session successfully altered.

Now I checked it through "select sysdate from dual"...and I got

29-11-06 as output. And tried again like

SQL> select TO_CHAR(29-11-06,'Month') as Now_Month from dual;

And I got error like..

ERROR at line 1:
ORA-01481: invalid number format model

What is the wrong with above query??....Is it possible to get the o/p without using TO_DATE within TO_CHAR for the above problem...

Thanks and waiting for kind cooperation

Report message to a moderator

Re: To_CHAR Problem [message #206308 is a reply to message #206306]

Wed, 29 November 2006 09:48

Cthulhu
Messages: 381
Registered: September 2006
Location: UK

Senior Member

It might help if you put your dates in single quotes:

select TO_CHAR('29-NOV-06','Month') as Now_Month from dual;

Report message to a moderator

Re: To_CHAR Problem [message #206309 is a reply to message #206306]

Wed, 29 November 2006 09:49

Frank
Messages: 7901
Registered: March 2000

Senior Member

What's wrong with black-on-white letters?

29-NOV-06 is not a date, the way you use it it is not even a string, it is just a couple of tokens to SQL.

to_char, as you said so yourself, takes either a number or a date as first parameter.
So:

Code: [Select all] [Show/ hide]

select to_char(to_date('29-NOV-06', 'dd-MON-rr'), 'Month') from dual;

In addition to that: years consist of FOUR digits. Remember 19100??

Report message to a moderator

Re: To_CHAR Problem [message #206316 is a reply to message #206309]

Wed, 29 November 2006 10:06

deep_gh1
Messages: 13
Registered: October 2006
Location: India

Junior Member

Thanks Frank...!!!

But if u dont like to tell 26-NOV-06 as a date what we got from
"select sysdate from dual"....Then what u would like say the output as -- sysdate(system date) or sysstring(System string)??

Report message to a moderator

Re: To_CHAR Problem [message #206320 is a reply to message #206316]

Wed, 29 November 2006 10:11

Frank
Messages: 7901
Registered: March 2000

Senior Member

If you issue a query in sqlplus, or any tool alike, it silently converts all output to strings.
The query "select sysdate from dual" really returns a date, not (easily) readable by us humans. Some thing between the database and your screen, there is a layer that converts this to a string.

Report message to a moderator

Re: To_CHAR Problem [message #206322 is a reply to message #206320]

Wed, 29 November 2006 10:18

deep_gh1
Messages: 13
Registered: October 2006
Location: India

Junior Member

Ok I got it,then if i use it as like below...

select TO_CHAR('29-NOV-06','Month') from dual;

then Why dont it considered as string as use it within qoute.Why do i get the error like..

"Invalid Number"...

Plz confirm me!!

Report message to a moderator

Re: To_CHAR Problem [message #206324 is a reply to message #206322]

Wed, 29 November 2006 10:22

Frank
Messages: 7901
Registered: March 2000

Senior Member

What should it return? to_char can create a character-string representation of either a date or a string containing a number
'29-NOV-06' is neither a date, nor a string containing a number. Oracle recognizes it as a string, so it presumes it contains a number, whence the 'Invalid number' error

Report message to a moderator

Re: To_CHAR Problem [message #206359 is a reply to message #206306]

Wed, 29 November 2006 13:40

Ronald Beck
Messages: 121
Registered: February 2003

Senior Member

A date field in Oracle is a decimal number with the portion left of the decimal representing the date and the portion right of the decimal representing the time.

When humans type in a date, we're typing in a string. Your statement...

Code: [Select all] [Show/ hide]

select TO_CHAR(sysdate,'Month') as Now_Month from dual;

works because the FUNCTION sysdate returns a numeric value. You use the TO_CHAR function to convert the number (the decimal date) to a string you can read. Furthermore, because of the format you selected, only the 'Month' portion gets displayed.

The value you entered, 29-NOV-06, is a STRING, not a decimal number representing a date. The command...

Code: [Select all] [Show/ hide]

select to_date('29-NOV-06','DD-MON-RR') from dual;

would provide you with the decimal date. The 'DD-MON-RR' is the format that your string is in. If you used '29-11-06', you would have to include the format string 'DD-MM-RR' because that's the order of your date string. '11/29/2006' would use a format string of 'MM/DD/RRRR' or 'MM/DD/YYYY'. To get just the month from that, you would have to do...

Code: [Select all] [Show/ hide]

select to_char(to_date('29-NOV-06',DD-MON-RR'),'Month') from dual;

which says, change the string '29-NOV-06' to a decimal date and then change that decimal date into a string containing the 'Month'.

If you always remember that the date you type in is a string and you have to change it to a decimal date (to_date) before you can manipulate it or enter it into a table, you'll have better results when working with dates.

Report message to a moderator

Re: To_CHAR Problem [message #206372 is a reply to message #206359]

Wed, 29 November 2006 15:07

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Well said!
I'd put in an appropriate emoticon like Littlefoot likes to do, but I'm a minimalist and I only use a smile and a frown and only in rare cases at that.

[Updated on: Wed, 29 November 2006 15:10]

Report message to a moderator

Re: To_CHAR Problem [message #206375 is a reply to message #206306]

Wed, 29 November 2006 15:51

Bill B
Messages: 1971
Registered: December 2004

Senior Member

As a matter of fact, one of my pet peeves is a programmer who relies on the default nls format for dates. Any time the date mask is changed by the application, client or database your application will break. ALWAYS use to_date and always supply a format mask and it is a good idea to always use the full year. I modified software for 3 years at an old employer because the previous programmers liked using 2 digit years and Y2K was coming up.

Report message to a moderator

Re: To_CHAR Problem [message #206413 is a reply to message #206359]

Thu, 30 November 2006 00:07

deep_gh1
Messages: 13
Registered: October 2006
Location: India

Junior Member

Thanks!!!..Nice contribution!!!

Report message to a moderator

Previous Topic:	update not happeing
Next Topic:	Column as rows in PL/SQL Select Statement

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 05 04:44:40 CDT 2025