Home » SQL & PL/SQL » SQL & PL/SQL » ORA 01847
ORA 01847 [message #678359] Wed, 27 November 2019 12:06 Go to next message
Satran
Messages: 2
Registered: November 2019
Junior Member
Hi guys ,
i am stuck here,

This sql line I am getting an error.

dat_chk is of date type.
Dat_chk is set to sysdate.
And dat_val is varchar2(1)


select to_char(dat_chk,D) into dat_val from dual;

Error received:
ORA- 01847 day of the month must be between 1 and last day of the month.

I tried many times with diff solutions , checking with To_date, changing nls paramters.
Re: ORA 01847 [message #678361 is a reply to message #678359] Wed, 27 November 2019 12:17 Go to previous messageGo to next message
Michel Cadot
Messages: 66875
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the 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.

You have to post the complete code you execute if you want we help you to debug it.

Re: ORA 01847 [message #678364 is a reply to message #678359] Wed, 27 November 2019 16:10 Go to previous messageGo to next message
BlackSwan
Messages: 26659
Registered: January 2009
Location: SoCal
Senior Member
Satran wrote on Wed, 27 November 2019 10:06
Hi guys ,
i am stuck here,

This sql line I am getting an error.

dat_chk is of date type.
Dat_chk is set to sysdate.
And dat_val is varchar2(1)


select to_char(dat_chk,D) into dat_val from dual;

Error received:
ORA- 01847 day of the month must be between 1 and last day of the month.

I tried many times with diff solutions , checking with To_date, changing nls paramters.

DT_VAL needs to be VARCHAR2(2) in order to hold value like 27;which is today's number value
Re: ORA 01847 [message #678368 is a reply to message #678364] Wed, 27 November 2019 23:07 Go to previous messageGo to next message
Satran
Messages: 2
Registered: November 2019
Junior Member
@Blackswan, thank you for taking your time to response, I am sorry but I am checking the days of the week in this condition, from 1 to 7, Sunday - 1 to Saturday - 7,

Basically I use this condition to determine the working day.

So its enough for 1 byte, I missed to add this information apologies.
Re: ORA 01847 [message #678371 is a reply to message #678368] Thu, 28 November 2019 00:12 Go to previous messageGo to next message
Michel Cadot
Messages: 66875
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You missed to post the code.

Re: ORA 01847 [message #678374 is a reply to message #678359] Thu, 28 November 2019 01:37 Go to previous messageGo to next message
John Watson
Messages: 8172
Registered: January 2010
Location: Global Village
Senior Member
It works for me:
orclz> var dat_val varchar2(1)
orclz> exec select to_char(sysdate,'D') into :dat_val from dual

PL/SQL procedure successfully completed.

orclz> print dat_val

DAT_VAL
--------------------------------
4

orclz>
Re: ORA 01847 [message #678376 is a reply to message #678374] Thu, 28 November 2019 04:48 Go to previous messageGo to next message
cookiemonster
Messages: 13826
Registered: September 2008
Location: Rainy Manchester
Senior Member
Just to be completely clear:
If dat_chk is date datatype
And it's set to sysdate
And it's actually that select that is throwing the error and not some other line of code.
Then the only explanation for that error is an oracle bug.

A more likely explanation is that one of your assumptions is wrong.
Which is why Michel asked you post the full code.
Re: ORA 01847 [message #678377 is a reply to message #678376] Thu, 28 November 2019 05:01 Go to previous messageGo to next message
_jum
Messages: 562
Registered: February 2008
Senior Member
May be because of incorrect date format:

select to_char(date '2019-11-28','D') from dual;

TO_CHAR(DATE'2019-11-28','D')
-----------------------------
4                            
1 row selected.


select to_char(date '28-11-2019','D') from dual;

Error at line 5
ORA-01847: day of month must be between 1 and last day of month


select date '28-11-2019' from dual;

Error at line 5
ORA-01847: day of month must be between 1 and last day of month

[Updated on: Thu, 28 November 2019 05:03]

Report message to a moderator

Re: ORA 01847 [message #678380 is a reply to message #678377] Thu, 28 November 2019 07:01 Go to previous messageGo to next message
cookiemonster
Messages: 13826
Registered: September 2008
Location: Rainy Manchester
Senior Member
If that's the problem then it would of course mean either:
dat_chk is not date datatype
or
it's not that select that's actually throwing the error.
Re: ORA 01847 [message #678388 is a reply to message #678380] Fri, 29 November 2019 07:36 Go to previous messageGo to next message
Bill B
Messages: 1938
Registered: December 2004
Senior Member
His problem is that he is using the following command

select to_char(dat_chk,D) into dat_val from dual;

and it should be

select to_char(dat_chk,'D') into dat_val from dual;

The second argument of the to_char command is the format mask and it must be enclosed in quotes
Re: ORA 01847 [message #678392 is a reply to message #678388] Fri, 29 November 2019 09:08 Go to previous message
cookiemonster
Messages: 13826
Registered: September 2008
Location: Rainy Manchester
Senior Member
That would give a completely different error:
SQL> select to_char(sysdate,D) from dual;
select to_char(sysdate,D) from dual

ORA-00904: "D": invalid identifier
So I assume the missing quotes are down to the OP manually typing example code here rather than copying and pasting the real code.

ETA: That's in SQL, in PL/SQL that just won't compile (unless you've got a variable/column called d).

[Updated on: Fri, 29 November 2019 09:10]

Report message to a moderator

Previous Topic: bulk collect problem through dblink
Next Topic: how to make table query in oracle (4 merged)
Goto Forum:
  


Current Time: Sat Feb 29 08:01:17 CST 2020