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)
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.
|
|
|
|
|
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: 68665 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:26Hi 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?
|
|
|
Goto Forum:
Current Time: Fri Jun 28 09:21:21 CDT 2024
|