Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01847 day of the month must be between 1 and last day of month
ORA-01847 day of the month must be between 1 and last day of month [message #192331] Mon, 11 September 2006 21:16 Go to next message
himjhamb
Messages: 1
Registered: September 2006
Junior Member
Hi,
I just signed on to this forum - am an application developer in need of some SQL help.

I am trying to execute the following SQL on an Oracle 9.x DB table.

SQL>
select id, timestamp from ICC
where
removed=0 and
stored=1 and
to_date(timestamp,'mm/dd/yyyy hh24:mi:ss') < to_date('09/07/2006 00:00:00','mm/dd/yyyy hh24:mi:ss')

The timestamp is a VARCHAR2 type column. I checked the data in the table - there are about 215 rows and all timestamp values look valid. But, I am getting the ORA-01847 error.

The strange part is: If I just rearrange the statements in the WHERE clause, I don't get this error, anymore. Instead, I get "No rows found". The rewritten SQL:

SQL>
select id, timestamp from ICC
where
to_date(timestamp,'mm/dd/yyyy hh24:mi:ss') < to_date('09/07/2006 00:00:00','mm/dd/yyyy hh24:mi:ss') and
removed=0 and
stored=1;

I even tried doing away with the "removed" and the "stored" conditions from the WHERE clause and got "No rows found".

SQL>
select id, timestamp from ICC
where
to_date(timestamp,'mm/dd/yyyy hh24:mi:ss') < to_date('09/07/2006 00:00:00','mm/dd/yyyy hh24:mi:ss')

So, My question is - why am I getting the ORA-01847 for the first SQL and not in the other two?

Thanks in Advance
- HJ

Re: ORA-01847 day of the month must be between 1 and last day of month [message #192333 is a reply to message #192331] Mon, 11 September 2006 22:37 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
This might be a help

http://www.orafaq.com/forum/t/53635/0/

Liza
icon14.gif  Re: ORA-01847 day of the month must be between 1 and last day of month [message #192485 is a reply to message #192331] Tue, 12 September 2006 10:05 Go to previous messageGo to next message
vjaynarwade
Messages: 5
Registered: September 2006
Location: Ramanthapur
Junior Member

Hi,

Try out changing the format of the date
as dd-mon-yyyy i.e dd/mon/yyyy.

Re: ORA-01847 day of the month must be between 1 and last day of month [message #192487 is a reply to message #192485] Tue, 12 September 2006 10:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What good would that do?
Re: ORA-01847 day of the month must be between 1 and last day of month [message #192514 is a reply to message #192487] Tue, 12 September 2006 12:51 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Your main problem is using a varchar2 vield to hold a timestamp!! A timstamp or date field should always be used to hold time/date information. Using a varchar2 field is just plain bad programming. There is NEVER a valid reason to store date/time as a character field. Even if a user is typing the information into a form, you do validation in the form before you store it in the database table.
Previous Topic: index on columns
Next Topic: create view dynamically
Goto Forum:
  


Current Time: Tue Dec 06 04:38:36 CST 2016

Total time taken to generate the page: 0.09253 seconds