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  |
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  |
 |
Littlefoot
Messages: 21808
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 #356999 is a reply to message #356907] |
Mon, 03 November 2008 08:18 |
joy_division
Messages: 4963
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 |
|
|
|
Goto Forum:
Current Time: Sun Apr 28 23:07:26 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
