Home » SQL & PL/SQL » SQL & PL/SQL » invalid number
invalid number [message #328611] Fri, 20 June 2008 15:37 Go to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

i need to get just the date part from this value.

SQL>  select trunc('02-JAN-2008 6:00') from dual;
 select trunc('02-JAN-2008 6:00') from dual
              *
ERROR at line 1:
ORA-01722: invalid number


Any help on this problem would be appreciate and thanks for you valuable time

Re: invalid number [message #328612 is a reply to message #328611] Fri, 20 June 2008 15:45 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Date part from a string? Can't do. It is equal to "how do I select date part from 'A223 kks -$$2((/'? Just a string, nothing more and nothing less.

If you want to deal with a date, well, you have to deal with a date, not a string:
SQL> select trunc(to_date('02-sij-2008 6:00', 'dd-mon-yyyy hh24:mi'))
  2  from dual;

TRUNC(TO_DATE('02-S
-------------------
02.01.2008 00:00:00

SQL>

You may apply TO_CHAR function to the result and format it as you wish, such as
SQL> select to_char(trunc(to_date('02-sij-2008 6:00', 'dd-mon-yyyy hh24:mi')),
  2                 'dd. month yyyy') result
  3  from dual;

RESULT
-----------------
02. siječanj 2008

SQL>

Re: invalid number [message #328614 is a reply to message #328612] Fri, 20 June 2008 15:52 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

First of thanks and appreciate your interest in my problem..

when i am trying to execute this query, i got the error like something like this ....

SQL> select to_char(trunc(to_date('02-sij-2008 6:00', 'dd-mon-yyyy hh24:mi')), 'dd. month yyyy') res
ult  from dual;
select to_char(trunc(to_date('02-sij-2008 6:00', 'dd-mon-yyyy hh24:mi')), 'dd. month yyyy') result  
                             *
ERROR at line 1:
ORA-01843: not a valid month
Re: invalid number [message #328615 is a reply to message #328614] Fri, 20 June 2008 15:55 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Unless you live in Croatia (and your NLS Oracle database settings are adjusted to it), "sij" (which represents first month of the year, "siječanj") is an invalid month.

Perhaps you should try with "jan" (as January) instead.
Re: invalid number [message #328617 is a reply to message #328615] Fri, 20 June 2008 16:16 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Thanks alot much appreciate help
Re: invalid number [message #328625 is a reply to message #328611] Fri, 20 June 2008 22:26 Go to previous messageGo to next message
renjithgopinath
Messages: 1
Registered: June 2008
Location: Chennai, India
Junior Member
I hope this is what you need.

sql> alter session set nls_date_format = 'dd-mm-yyyy';

Session altered

sql> select to_date('02-JAN-2008 6:00','DD-MON-YYYY HH:MI') from dual;

TO_DATE('0
----------
02-01-2008
Re: invalid number [message #328636 is a reply to message #328625] Sat, 21 June 2008 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, this is NOT what is needed.
NEVER rely on implicit conversion.
ALWAYS give the format you want if it is necessary.

Regards
Michel
Re: invalid number [message #328859 is a reply to message #328625] Mon, 23 June 2008 02:12 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
renjithgopinath wrote on Sat, 21 June 2008 05:26
I hope this is what you need.

sql> alter session set nls_date_format = 'dd-mm-yyyy';

Session altered

sql> select to_date('02-JAN-2008 6:00','DD-MON-YYYY HH:MI') from dual;

TO_DATE('0
----------
02-01-2008

What is returned here is NOT only the date-part, but the complete date. SQLPlus manipulated that date only to display it.
If however you would use your method and processed the result further, you would still have the 06:00 part included.
Previous Topic: How to select the database objects
Next Topic: Index
Goto Forum:
  


Current Time: Sat Dec 10 03:27:21 CST 2016

Total time taken to generate the page: 0.10203 seconds