Home » SQL & PL/SQL » SQL & PL/SQL » Need help with Date format
Need help with Date format [message #279474] Thu, 08 November 2007 12:26 Go to next message
californiagirl
Messages: 79
Registered: May 2007
Member
I'm using this in my where clause:
AND C.check_date BETWEEN '9/1/1999' AND '9/11/1999'

I get an error message in Toad that says not a valid month.

check_date = Date in the table. Dates appear in this column as 9/1/1999.

What is the correct format for this date issue to work?

Re: Need help with Date format [message #279475 is a reply to message #279474] Thu, 08 November 2007 12:30 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member


Use to_date function.
Toad does not convert character to date automatically.

so use this:
 AND C.check_date BETWEEN to_date('09/01/1999','dd/mm/rrrr') AND to_date('09/11/1999','dd/mm/rrrr')

It will work.

Cheers
Soumen
Re: Need help with Date format [message #279476 is a reply to message #279474] Thu, 08 November 2007 12:30 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
In Oracle characters between single quote marks are STRINGS!
'This is a string datatype and not a DATE 9/11/1999'
When you want a DATE datatype use TO_DATE with a proper format mask!

[Updated on: Thu, 08 November 2007 12:31] by Moderator

Report message to a moderator

Re: Need help with Date format [message #279477 is a reply to message #279476] Thu, 08 November 2007 12:41 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
I still got the same error when I changed the format:

AND   C.check_date BETWEEN to_date('9/01/1999','dd/mm/rrrr') AND to_date('10/29/1999','dd/mm/rrrr') 


I even tried 'dd/mon/yyyy' and it still didn't like it.

Re: Need help with Date format [message #279480 is a reply to message #279474] Thu, 08 November 2007 12:52 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
On what planet is mm=29 a valid month?
Re: Need help with Date format [message #279481 is a reply to message #279480] Thu, 08 November 2007 12:59 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
Good point! It works now.

Thanks
Re: Need help with Date format [message #279482 is a reply to message #279474] Thu, 08 November 2007 13:06 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to realize that Oracle error message are correct 99.9999999999% of the time.
When it reports an invalid month, you have an invalid month!
Here is how I found it.

SQL> select to_date('10/29/1999','dd/mm/rrrr') 
  2  from dual;
select to_date('10/29/1999','dd/mm/rrrr')
               *
ERROR at line 1:
ORA-01843: not a valid month

SQL> select to_date('10/29/1999','mm/dd/rrrr') from dual;

TO_DATE('10/29/1999
-------------------
1999-10-29 00:00:00

SQL> 
Re: Need help with Date format [message #279483 is a reply to message #279474] Thu, 08 November 2007 13:58 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
One would think that after all of your other posts with using strings and DATEs, getting errors and being pointed to the correct way to use them, you would have learned by now.
Re: Need help with Date format [message #279484 is a reply to message #279474] Thu, 08 November 2007 14:11 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You can lead some folks to knowledge, but you can't make them think.
Previous Topic: Multi packages or single package? Which is good?
Next Topic: Return data from select table or View
Goto Forum:
  


Current Time: Sun Dec 04 12:35:15 CST 2016

Total time taken to generate the page: 0.07803 seconds