Home » SQL & PL/SQL » SQL & PL/SQL » date problem
date problem [message #223760] Sun, 11 March 2007 03:19 Go to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

Hii All

see this

select dateg from msexxxxxx
where no ='19777'


it results
01-JAN-07


 select to_char (dateg,'DD-MM-YYYY') 
 from msexxxxxx
 where no ='19777';


it results
01-01-2007



but when i put the date (of the first query) by hand like this

 select to_char ('01-JAN-07','DD-MM-YYYY') 
 from msexxxxxx
 where no ='19777';


it gives me an error

ERROR at line 1:
ORA-01722: invalid number


why there is an error like that although the result of the first
query i put it in the third



Thanks for every on helped and helping me

[Updated on: Sun, 11 March 2007 03:20]

Report message to a moderator

Re: date problem [message #223763 is a reply to message #223760] Sun, 11 March 2007 03:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That is because '01-JAN-07' is NOT a date. It is a string. to_char works on dates or numbers, so it will not work on '01-JAN-07'.
Read about to_char and to_date in sql manual.
Re: date problem [message #223767 is a reply to message #223760] Sun, 11 March 2007 03:57 Go to previous messageGo to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

ok that's right

but suppose i used '01-01-2007' instead of '01-JAN-2007'

so that the query will be

select to_char ('01-JAN-07','DD-MM-YYYY') 
 from msexxxxxx
 where no ='19777';



it gives me the same error

what is the datatype here
to_char ('01-JAN-07','DD-MM-YYYY')

does it is a number???

and if it is a number (due to the error of 'invalid number')

why it accepts dates
like that query
select to_char (dateg,'DD-MM-YYYY') 
 from msexxxxxx
 where no ='19777';


dateg here is a date data type

Re: date problem [message #223770 is a reply to message #223763] Sun, 11 March 2007 04:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Frank wrote on Sun, 11 March 2007 09:34
to_char works on dates or numbers, so it will not work on '01-JAN-07'.
Read about to_char and to_date in sql manual.


'01-JAN-07' is neither a date, nor a number.
'01-01-2007': same story.
If you want to convert your string '01-JAN-07' to the string '01-01-2007', you first have to convert it to a date:

to_char(to_date('01-JAN-07', 'DD-MON-RR'), 'DD-MM-YYYY')

You have to realize that the display of the outcome of your very first query is NOT a date; dates are not human-readable, so sqlplus does an implicit to_char, making the displayed value a string.
Re: date problem [message #223773 is a reply to message #223770] Sun, 11 March 2007 05:17 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
you keep using something like this:
('01-JAN-07','DD-MM-YYYY')
MM is not a valid format for JAN, but MON is. After you fix it, use valid function. Frank told you which ones to use; when and how to use any of them, read in SQL functions section of the Oracle Documentation.

Although it is possible to learn things using your method, perhaps it is better (and less time consuming) if you, actually, read about it.
Re: date problem [message #223784 is a reply to message #223767] Sun, 11 March 2007 07:43 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Like Frank and Littlefoot allready pointed out (maybe in other words) :
if you using to_date('01-01-2007','DD-MON-YYYY') you are telling oracle to use the function to_date with the literal string '01-01-2007'. This is not a piece of data with a date-type or a number which to_date is expecting.

Do as Frank and Littlefoot told you : read some documentation.
Re: date problem [message #223974 is a reply to message #223784] Mon, 12 March 2007 08:30 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
And I can't believe that after 280 messages you still don't know what a DATE is.
Previous Topic: To retrieve Table names and no of Rows in each table
Next Topic: To compute total amount in one SQL statement
Goto Forum:
  


Current Time: Sat Dec 10 22:14:09 CST 2016

Total time taken to generate the page: 0.07493 seconds