OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » date problem

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

date problem [message #223760]

Sun, 11 March 2007 03:19

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

Frank
Messages: 7901
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.

Report message to a moderator

Re: date problem [message #223767 is a reply to message #223760]

Sun, 11 March 2007 03:57

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

Report message to a moderator

Re: date problem [message #223770 is a reply to message #223763]

Sun, 11 March 2007 04:20

Frank
Messages: 7901
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.

Report message to a moderator

Re: date problem [message #223773 is a reply to message #223770]

Sun, 11 March 2007 05:17

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: date problem [message #223784 is a reply to message #223767]

Sun, 11 March 2007 07:43

martijn
Messages: 286
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.

Report message to a moderator

Re: date problem [message #223974 is a reply to message #223784]

Mon, 12 March 2007 08:30

joy_division
Messages: 4963
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.

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 01 11:13:28 CST 2024