Home » SQL & PL/SQL » SQL & PL/SQL » To_CHAR Problem
| 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
|
|
|
|
| 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;
|
|
|
|
| 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:
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 #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.
|
|
|
|
|
|
| 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
|
|
|
|
| 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...
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 #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.
|
|
|
|
|
|
Goto Forum:
Current Time: Thu May 15 16:27:44 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
