ORA 01847 [message #678359] |
Wed, 27 November 2019 12:06 |
|
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 #678374 is a reply to message #678359] |
Thu, 28 November 2019 01:37 |
John Watson
Messages: 8931 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 |
cookiemonster
Messages: 13923 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 |
_jum
Messages: 577 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 |
cookiemonster
Messages: 13923 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 |
Bill B
Messages: 1971 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 |
cookiemonster
Messages: 13923 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
|
|
|