asked on

error display as not a valid month while executing the query

errror display as not a valid month while executing the querry

/* Formatted on 2018/09/18 13:55 (Formatter Plus v4.8.8) */
SELECT a.dat, b.division, b.sm_name, b.dt, b.cust_code
FROM (SELECT dat
FROM (SELECT TO_DATE (:r_from_cust_main_acnt_code,
'DD/MM/RRRR'
)
+ ROWNUM
- 1 dat
FROM ot_job_exec_head
CONNECT BY LEVEL <=
TO_DATE (:r_till_cust_main_acnt_code,
'DD/MM/RRRR'
)
- TO_DATE (:r_from_cust_main_acnt_code,
'DD/MM/RRRR'
)
+ 1)
WHERE ROWNUM <= 31) a
LEFT JOIN
(SELECT division, sm_name, dt, cust_code
FROM (SELECT division, sm_name,
TO_CHAR (TRUNC (TO_DATE (dat, 'DD-MM-YYYY')),
'DD-Mm'
) dt,
cust_code
FROM (SELECT vssv_field_02 division, vssv_name sm_name,
jeh_dt dat, jed_flex_01 cust_code,
jed_flex_03 purpose, jed_flex_11 next_action
FROM ot_job_exec_head,
ot_job_exec_detail,
om_customer,
im_vs_static_value
WHERE jeh_sys_id = jed_jeh_sys_id
AND jed_code IN
('IC1155', 'IC1254', 'IC1216', 'IC1165',
'IC1246', 'IC1258', 'IC1222', 'IC1271',
'IC1185', 'IC1231', 'IC1286', 'IC1287',
'IC1288', 'IC1280', 'IC1283', 'IC1180',
'IC1291', 'IC1262')
AND vssv_vs_code = 'EMPLOYEE'
AND jeh_dt
BETWEEN TO_DATE
(:r_from_cust_main_acnt_code,
'DD/MM/RRRR'
)
AND TO_DATE
(:r_till_cust_main_acnt_code,
'DD/MM/RRRR'
)
AND NVL (vssv_frz_flag_num, 2) = 2
AND jed_code = vssv_code
AND jeh_dt > '31-MAR-2018'
AND jed_flex_01 = cust_code))) b ON a.dat = b.dt

Helena Marková

At first I would use the same date format mask. You have
'DD-MM-YYYY'
'DD/MM/RRRR'
and
AND jeh_dt > '31-MAR-2018'

select to_char(sysdate,'dd.mm.rrrr'),to_char(sysdate,'dd-mm-yyyy'),to_char(sysdate,'dd-mon-yyyy') from dual

Martyn Spencer

What value are you providing as :r_from_cust_main_acnt_code? If you are specifying it as a date value in the TOAD parameters, change your date format to MM/DD/YYYY or change the parameter to a string type. I have seen problems with parameter substitution and dates before.

Being in the UK I see this error quite often and it is usually because there is a mix-up with DD/MM/YYYY formatted dates and MM/DD/YYYY formatted dates.

ASKER CERTIFIED SOLUTION

Sujith S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martyn Spencer

To be frank, the accepted solution above simply changes the date format to a non-ambiguous format. Out of context it does not really answer anything. It would have helped the quality of this question had the author answered my question.

slightwv (䄆 Netminder)

The accepted answer isn't an answer.

You can't have:
TO_DATE (:r_from_cust_main_acnt_code, 'DD/MON/RRRR')
and
TO_DATE (:r_from_cust_main_acnt_code, 'DD/MM/RRRR')

They both cannot be correct.

Since I'm here: "AND jeh_dt > '31-MAR-2018'"

You should never rely on explicit data type conversions. ALWAYS use explicit: AND jeh_dt > to_date('31-MAR-2018','DD-MON-YYYY')

Martyn Spencer

I had not even noticed that further down there is a completely different format used so I agree, this is definitely not a solution. Good catch slightwv.