Home » SQL & PL/SQL » SQL & PL/SQL » ora-01839 : date not valid for month specified
ora-01839 : date not valid for month specified [message #356907] Mon, 03 November 2008 01:42 Go to next message
iriswancy
Messages: 17
Registered: December 2005
Location: Malaysia
Junior Member
Hi,

When i tried to execute the following sql, i keep encounter this error ORA-01839 : Date not valid for month specified.

SQL :

select agent_code,agent_name,nric_new,nric_old,agent_rank,agent_status,agent_center_code,
service_off,fmutm_no,to_date(original_exp_dt,'dd-mm-yyyy') as original_exp_dt,fmutm_exp_dt,fee_amount,tran_date from (
WITH ORI_EXP_DT
AS
(SELECT a.agent_code,max(b.fmutm_exp_dt) as original_exp_dt
from t_reg_agent a,t_renewal_tran b
where a.rectype = 'L'
and a.agent_code = b.agent_code
and b.fmutm_exp_dt < a.fmutm_exp_dt
GROUP BY a.agent_code)
select g.agent_code, g.agent_name, g.nric_new,g.nric_old,
g.agent_rank,g.agent_status,g.agent_center_code,s.centre_desc as service_off,g.FMUTM_No,
--to cater agents for the 1st time renewal where fmutm original exp date will be overwrite
case when to_char(decode(c.ORIGINAL_EXP_DT,null,add_months(g.dt_join,11)),'mm') in ('02','04','06','09','11')
then '30-'||to_char(decode(c.ORIGINAL_EXP_DT,null,add_months(g.dt_join,11)),'mm-yyyy')
when to_char(decode(c.ORIGINAL_EXP_DT,null,add_months(g.dt_join,11)),'mm') in ('01','03','05','07','08','10','12')
then '31-'||to_char(decode(c.ORIGINAL_EXP_DT,null,add_months(g.dt_join,11)),'mm-yyyy')
else to_char(c.ORIGINAL_EXP_DT,'dd-mm-yyyy') end as ORIGINAL_EXP_DT,
g.fmutm_exp_dt, --to get the latest expiry_date
h.fee_amount,t.tran_date
from
t_renewal_req t,--only contain manual payment
t_reg_agent g,
t_renewal_tran h,
t_service_centre s,
ori_exp_dt c --to get original fmutm exp date
where
t.rectype='L'
and t.agent_code = h.agent_code
and t.renewal_ref_no = h.renewal_ref_no
and t.agent_code = g.agent_code and g.recType = 'L'
and t.action='Y'
and s.rectype = 'L'
and s.centre_code = g.agent_center_code
and c.agent_Code (+) = g.agent_code
ORDER BY G.AGENT_NAME) where ORIGINAL_EXP_DT between '01-11-2008' and '30-11-2008'

Please help as i cant figure out what is the problem here. As i even have tried to put the date range format between '01-Now-2008' and '30-Nov-2008'.

Thanks $ Regards,
Iris



Re: ora-01839 : date not valid for month specified [message #356908 is a reply to message #356907] Mon, 03 November 2008 01:45 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It usually happens when invalid dates are used, such as 30th of February:
SQL> select to_date('30-feb-2008', 'dd-mon-yyyy') From dual;
select to_date('30-feb-2008', 'dd-mon-yyyy') From dual
               *
ERROR at line 1:
ORA-01839: date not valid for month specified


SQL>

So, did you use valid dates?

[EDIT] Forgot to say: read the OraFAQ Forum Guide; such an unformatted code is difficult to understand. The Guide will teach you how to write an acceptable message.

[Updated on: Mon, 03 November 2008 04:28]

Report message to a moderator

Re: ora-01839 : date not valid for month specified [message #356925 is a reply to message #356908] Mon, 03 November 2008 04:19 Go to previous messageGo to next message
iriswancy
Messages: 17
Registered: December 2005
Location: Malaysia
Junior Member
Hi,

Ok...I understood now and i have rectifed my code already.

Thanks a lot : ) !!

Thanks & Regards,
Iris Wan
Re: ora-01839 : date not valid for month specified [message #356999 is a reply to message #356907] Mon, 03 November 2008 08:18 Go to previous message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
iriswancy wrote on Mon, 03 November 2008 02:42

ORDER BY G.AGENT_NAME) where ORIGINAL_EXP_DT between '01-11-2008' and '30-11-2008'



But that doesn't even begin to solve your fundamental flaw of comparing DATEs to STRINGS.
FOO SCOTT>select 1 from dual where sysdate between '01-11-2008' and '30-11-2008';
select 1 from dual where sysdate between '01-11-2008' and '30-11-2008'
                                                          *
ERROR at line 1:
ORA-01843: not a valid month


Please refer to the function TO_DATE in the documentation, and it's proper use in queries.

[Updated on: Mon, 03 November 2008 08:19]

Report message to a moderator

Previous Topic: Storing gifs
Next Topic: ORA-20000: Object ORDER does not exist or insufficient
Goto Forum:
  


Current Time: Wed Dec 07 12:47:16 CST 2016

Total time taken to generate the page: 0.11493 seconds