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

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01847: day of month must be between 1 and last day of month 01847. 00000 - "day of month must b (Oracle)

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 01847. 00000 - "day of month must b [message #655916]

Fri, 16 September 2016 04:26

ROHIN
Messages: 1
Registered: September 2016

Junior Member

Hi Team,

In table one of the column data is 15/9/16. But i need data like 15-sep-16

I am trying below query
select * from
TO_CHAR(TO_DATE(REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,7),'[^,]+',1,1) ,'DD-MM-YY') ,'DD-MON-YY') C07
from dual;
But i am getting below error ORA-01847: day of month must be between 1 and last day of month
01847. 00000 - "day of month must be between 1 and last day of month".

Could you please some one can help on this this is urgent requirement i tried from last 2 days.

Regards,
Rohin.

Report message to a moderator

Re: ORA-01847: day of month must be between 1 and last day of month 01847. 00000 - "day of month must b [message #655919 is a reply to message #655916]

Fri, 16 September 2016 05:47

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

TO_CHAR(TO_DATE) should do the job:

SQL> with test as
  2    (select '15/9/16' col from dual)
  3  select to_char(to_date(col, 'dd/mm/yy'), 'dd-mon-yy') result
  4  from test;

RESULT
---------
15-sep-16

Report message to a moderator

Re: ORA-01847: day of month must be between 1 and last day of month 01847. 00000 - "day of month must b [message #655921 is a reply to message #655919]

Fri, 16 September 2016 06:23

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

What datatype is SUSPEND_DATA1?

Report message to a moderator

Re: ORA-01847: day of month must be between 1 and last day of month 01847. 00000 - "day of month must b [message #655924 is a reply to message #655916]

Fri, 16 September 2016 09:56

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

SQL> select * from
  2  TO_CHAR(TO_DATE(REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,7),'[^,]+',1,1) ,'DD-MM-YY') ,'DD-MON-YY') C07
  3  from dual;
TO_CHAR(TO_DATE(REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,7),'[^,]+',1,1) ,'DD-MM-YY') ,'DD-MON-YY') C07
       *
ERROR at line 2:
ORA-00933: SQL command not properly ended

Copy and paste what you actually did and got.

[Updated on: Fri, 16 September 2016 09:58]

Report message to a moderator

Re: ORA-01847: day of month must be between 1 and last day of month 01847. 00000 - "day of month must b [message #655926 is a reply to message #655916]

Fri, 16 September 2016 10:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ROHIN wrote on Fri, 16 September 2016 02:26

Hi Team,

In table one of the column data is 15/9/16. But i need data like 15-sep-16

I am trying below query
select * from
TO_CHAR(TO_DATE(REGEXP_SUBSTR(REGEXP_SUBSTR(REPLACE(SUSPEND_DATA1,':','|'),'[^|]+',1,7),'[^,]+',1,1) ,'DD-MM-YY') ,'DD-MON-YY') C07
from dual;
But i am getting below error ORA-01847: day of month must be between 1 and last day of month
01847. 00000 - "day of month must be between 1 and last day of month".

Could you please some one can help on this this is urgent requirement i tried from last 2 days.

Regards,
Rohin.

What is origin of SUSPEND_DATA1 in code posted above?
why are you selecting from dual?

why is it urgent for us to solve this for you?

Report message to a moderator

Previous Topic:	Enforce blank record
Next Topic:	Dynamic SQL : ORA-01006: bind variable does not exist (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 20:17:11 CDT 2024