Home » SQL & PL/SQL » SQL & PL/SQL » Problem in formatting Date
Problem in formatting Date [message #386757] Mon, 16 February 2009 23:18 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Could any one help me in Date function for below query.


select to_char('13-Jun-08  09:15:20', 'dd-month-yy  hh24:mi:ss') from dual;

ERROR at line 1:
ORA-01722: invalid number




Thanks

[MERGED by LF]

[Updated on: Tue, 17 February 2009 00:10] by Moderator

Report message to a moderator

Re: problem in formatting Date [message #386759 is a reply to message #386757] Mon, 16 February 2009 23:24 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
data does not match format mask.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you

>Could any one help me in Date function for below query.
ensure data EXACTLY matches format mask.
Re: problem in formatting Date [message #386761 is a reply to message #386759] Mon, 16 February 2009 23:27 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Use TO_DATE function instead of TO_CHAR.


SELECT TO_DATE ('13-Jun-08 09:15:20', 'dd-mon-yy hh24:mi:ss')
  FROM DUAL;


Thanks
Trivendra
Problem with date [message #386762 is a reply to message #386757] Mon, 16 February 2009 23:29 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

Hi,

I am not able to pull the correct report for below queries.

When I am executing first query no rows returned and while executing 2nd qury i am getting records before '01-OCT-07' also


select * from samp where 
to_char(created,'dd-mon-yy  hh24:mi:ss') < '01-OCT-07 00:00:00';

select * from samp where 
to_char(created,'dd-mon-yy  hh24:mi:ss') > '01-OCT-07 00:00:00';



Any help really appreciated.

Thanks
Re: Problem with date [message #386763 is a reply to message #386762] Mon, 16 February 2009 23:32 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you

>When I am executing first query no rows returned and while executing 2nd qury i am getting records before '01-OCT-07' also

Do not compare strings;compare dates.

[Updated on: Mon, 16 February 2009 23:33]

Report message to a moderator

Re: Problem with date [message #386764 is a reply to message #386762] Mon, 16 February 2009 23:33 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
1. '01-OCT-07 00:00:00' is not a date it is character. convert the string to date using TO_DATE function.

2. Don't convert CREATED column to string. let it be a DATE type.


SELECT * FROM samp WHERE created < TO_DATE ('01-OCT-07 00:00:00', 'DD-MON-YY  HH24:MI:SS');
  


Thanks
Trivendra
Re: Problem with date [message #386767 is a reply to message #386762] Mon, 16 February 2009 23:36 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
trivendra,

From URL below
http://www.orafaq.com/forum/t/88153/0/

It states
"When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation. "

Please cease & desist feeding newbies complete answers!
Re: problem in formatting Date [message #386770 is a reply to message #386757] Mon, 16 February 2009 23:40 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
vikram1780,

Stop spamming forum especially after you've been provided a complete solution to this FAQ!
Re: problem in formatting Date [message #386837 is a reply to message #386770] Tue, 17 February 2009 02:39 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
trivendra has given you the key. But allow me to ellaborate and let me tell you how I read this query.
select * 
from samp 
where to_char(created,'dd-mon-yy  hh24:mi:ss') < '01-OCT-07 00:00:00';


1. select all columns from table samp.
So far so good. Nothing wrong with that.
2. But only select the records where the string you can make of the date column 'created' is less than '01-OCT-07 00:00:00'.
Less than...string? How does Oracle compare strings?
Well, <string1> 'less than' <string2> means: '<string1> has a lower ascii value than <string2>'. And here comes error number one: you convert the date column 'created' to a string consisting of 'day of the month', lowercase month name abbreviation and the year. And you want to compare that string to something that appears to be a date (with an uppercase month name in it) but isn't. You compare string ascii values, while you want to compare dates. It is a common mistake, but you need to work from the other direction: convert your string to a date and compare that to the date column value.

There's an extra error in your logic: you compare a lowercase month abbreviated name to an uppercase month abbreviated name, or so it appears to be. I cannot be sure, since you're comparing strings.

And if you want to compare years, you might want to use full years instead of only 2 characters of the year. You might end up with strange results...

MHE
Previous Topic: NOT NULL Constraint Oracle SQL
Next Topic: How to access table data through a variable
Goto Forum:
  


Current Time: Sat Dec 03 11:54:31 CST 2016

Total time taken to generate the page: 0.08689 seconds