OraFAQ Forum: SQL & PL/SQL » ORA-01847: day of month must be between 1 and last day of month

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01847: day of month must be between 1 and last day of month

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

ORA-01847: day of month must be between 1 and last day of month [message #142923]

Tue, 18 October 2005 07:38

vishnus
Messages: 7
Registered: October 2005
Location: India

Junior Member

Hi,
I have been recieving error "ORA-01847: day of month must be between 1 and last day of month" on production server and when I process same data on our local server it gets processed successfully.

The statement that causing an issue is listed below.
I found this is the statement which is causing issue by debug statements.

update temp
SET CALL_START_TIME = TO_DATE((TO_CHAR(TRUNC(CALL_DATE), 'DDMMYYYY') || TO_CHAR(CALL_TIME, 'HH24MISS')), 'DDMMYYYYHH24MISS')
where filenum=fileid

Query seems very simple and above Query works fine other data and fails for fewer data.

I have checked there is nothing wrong in data.

Pls help me out

Thanks

Report message to a moderator

Re: ORA-01847: day of month must be between 1 and last day of month [message #142926 is a reply to message #142923]

Tue, 18 October 2005 07:44

bonker
Messages: 402
Registered: July 2005

Senior Member

describe your temp table.

Report message to a moderator

Re: ORA-01847: day of month must be between 1 and last day of month [message #142944 is a reply to message #142923]

Tue, 18 October 2005 08:12

vishnus
Messages: 7
Registered: October 2005
Location: India

Junior Member

hi,

Temp(call_date date, call_start_time date, call_time date);

[Updated on: Tue, 18 October 2005 08:13]

Report message to a moderator

Re: ORA-01847: day of month must be between 1 and last day of month [message #142948 is a reply to message #142944]

Tue, 18 October 2005 08:14

bonker
Messages: 402
Registered: July 2005

Senior Member

Is your call_time passed as parameter? If yes what is its data type?

Report message to a moderator

Re: ORA-01847: day of month must be between 1 and last day of month [message #142966 is a reply to message #142948]

Tue, 18 October 2005 08:50

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

How about making the update less messy with this?

update temp
SET CALL_START_TIME = CALL_TIME - (trunc(call_time) - trunc(call_date))
where filenum=fileid

Report message to a moderator

Re: ORA-01847: day of month must be between 1 and last day of month [message #143353 is a reply to message #142966]

Thu, 20 October 2005 02:02

vishnus
Messages: 7
Registered: October 2005
Location: India

Junior Member

Thanks for ur Solution.

I applied the changes and till now I have not recieved error but what could be the cause.

Whats wrong in my Query.

Report message to a moderator

Re: ORA-01847: day of month must be between 1 and last day of month [message #143449 is a reply to message #143353]

Thu, 20 October 2005 08:43

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

???

What do you mean? If you have not received an error what could be the cause??? Cause of what?

Or do you mean it is still running? If your table is big and you have no indexes, there is no telling how long it will take to run. You have supplied no information.

Report message to a moderator

Re: ORA-01847: day of month must be between 1 and last day of month [message #143906 is a reply to message #143449]

Mon, 24 October 2005 01:38

vishnus
Messages: 7
Registered: October 2005
Location: India

Junior Member

I think you got it wrong..

The Query by converting date and date with timestamp to varchar ,then concatenate and convert back to date was throwing an exception, I really wanted to know the output of both the Queries should be same and ideally should not throw an exeption.

And I wanted to know the reason behind and exception..

thanks for ur help

Report message to a moderator

Re: ORA-01847: day of month must be between 1 and last day of month [message #143932 is a reply to message #143906]

Mon, 24 October 2005 04:43

Frank
Messages: 7901
Registered: March 2000

Senior Member

I can reproduce it if call_date is null and if I use another date-format:

create table mytab 
( call_date       date
, call_start_time date
, call_time       date
)
/
insert into mytab
( call_date
, call_start_time
, call_time
) values
( null
, null
, to_date('01-01-2000 12:56:23', 'dd-mm-yyyy hh24:mi:ss')
)
/

update mytab
set    call_start_time = to_date( (to_char(call_date, 'MMDDYYYY')||to_char(call_time, 'HH24MISS'))
                                , 'MMDDYYYYHH24MISS')
/

ERROR at line 2:
ORA-01847: day of month must be between 1 and last day of month

Note that I changed the date-format from DDMM to MMDD. (in both cases naturally)
The reason is that the minutes-part of the call_time is seen as the days-part of the whole. This is the reason I can only get it by changing the date-format.

Are you sure you use the date-format you indicated, or is it a simplified version of your production software?

hth

Report message to a moderator

Previous Topic:	ORA-01722: invalid number
Next Topic:	question on tuples and rows

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 06:44:38 CDT 2024