Home » SQL & PL/SQL » SQL & PL/SQL » To_CHAR Problem
To_CHAR Problem [message #206306] Wed, 29 November 2006 09:45 Go to next message
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

Re: To_CHAR Problem [message #206308 is a reply to message #206306] Wed, 29 November 2006 09:48 Go to previous messageGo to next message
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;
Re: To_CHAR Problem [message #206309 is a reply to message #206306] Wed, 29 November 2006 09:49 Go to previous messageGo to next message
Frank
Messages: 7880
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:
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??
Re: To_CHAR Problem [message #206316 is a reply to message #206309] Wed, 29 November 2006 10:06 Go to previous messageGo to next message
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)??
Re: To_CHAR Problem [message #206320 is a reply to message #206316] Wed, 29 November 2006 10:11 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: To_CHAR Problem [message #206322 is a reply to message #206320] Wed, 29 November 2006 10:18 Go to previous messageGo to next message
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!!
Re: To_CHAR Problem [message #206324 is a reply to message #206322] Wed, 29 November 2006 10:22 Go to previous messageGo to next message
Frank
Messages: 7880
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
Re: To_CHAR Problem [message #206359 is a reply to message #206306] Wed, 29 November 2006 13:40 Go to previous messageGo to next message
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...

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...

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...

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.

Re: To_CHAR Problem [message #206372 is a reply to message #206359] Wed, 29 November 2006 15:07 Go to previous messageGo to next message
joy_division
Messages: 4640
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 Go to previous messageGo to next message
Bill B
Messages: 1482
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.
Re: To_CHAR Problem [message #206413 is a reply to message #206359] Thu, 30 November 2006 00:07 Go to previous message
deep_gh1
Messages: 13
Registered: October 2006
Location: India
Junior Member

Thanks!!!..Nice contribution!!!
Previous Topic: update not happeing
Next Topic: Column as rows in PL/SQL Select Statement
Goto Forum:
  


Current Time: Sat Dec 03 12:10:51 CST 2016

Total time taken to generate the page: 0.12951 seconds