ORA-01847: day of month must be between 1 and last day of month

I got ” ORA-01847: day of month must be between 1 and last day of month ”  error in Oracle database.

 

ORA-01847: day of month must be between 1 and last day of month

 

Details of error are as follows.

ORA-01847 day of month must be between 1 and last day of month

Cause: The day of the month listed in a date is invalid for the specified month. 
The day of the month (DD) must be between 1 and the number of days in that month.

Action: Enter a valid day value for the specified month.

SELECT TO_DATE('2021/04/31', 'yyyy/mm/dd') FROM dual;

 

 

 

 

day of month must be between 1 and last day of month

This ORA-01847 error is related with the day of the month listed in a date is invalid for the specified month.
The day of the month (DD) must be between 1 and the number of days in that month.

Enter a valid day value for the specified month.

 

If you run the following code, you will get this error.

SELECT TO_DATE('2021/04/31', 'yyyy/mm/dd') FROM dual;
SELECT TO_DATE('2020/12/32', 'yyyy/mm/dd') FROM dual;

 

Because There is no 31 day in the April, and 32 day in December,  Last day is 30 in April and 31 is in December, so you need to use it as follows.

SELECT TO_DATE('2021/04/30', 'yyyy/mm/dd') FROM dual;
SELECT TO_DATE('2020/12/31', 'yyyy/mm/dd') FROM dual;

 

 

 

You can read the following post to learn more details about TO_DATE Function.

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

 117 views last month,  4 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply