Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01843: not a valid month
ORA-01843: not a valid month [message #18945] |
Fri, 22 February 2002 00:37 |
Rm69
Messages: 39 Registered: January 2002
|
Member |
|
|
ORA-01843: not a valid month
and this is the part its complaining about. When l remove to date on sysdate it works. What l want to know is why
would it give me that error now when all along its been working fine?
SELECT COUNT (*)
INTO v_cnt
FROM mis_wrkflw_pdincl_hist
WHERE TO_DATE (v_start_date, 'yyyy-mm-dd') =
TO_DATE (captured, 'yyyy-mm-
dd')
AND TO_DATE (SYSDATE, 'yyyy-mm-dd') =
TO_DATE (summary_date, 'yyyy-mm-
dd');
q2) How can l make sure that when l select records from say the 19th to the 20th day l get all the records? Do l have to specify the time in mins and hrs?
Here is the script from procedure:
PROCEDURE mis_newbusinsert_proc (
v_start_date DATE,
v_end_date DATE
)
IS
v_cnt NUMBER;
BEGIN
----------------------------------------------------------------------------
----
-- OTHER -----------------------------------------------------------------
----
----------------------------------------------------------------------------
----
-- QUERY TO SUM CAPITAL ON EDCON --
INSERT INTO mis_newbusiness
(branch, edcon, edcon_count, summary_date, captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE brokercd = 'KIOSKS'
AND disdate BETWEEN v_start_date AND v_end_date
OR loanoff IN ('153',
'1114',
'97',
'4843',
'4849',
'1157',
'E0080',
'J0196'
)
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
AND company NOT IN ('002')
GROUP BY branch;
----------------------------------------------------------------------------
----
-- CHANNELS --------------------------------------------------------------
----
----------------------------------------------------------------------------
----
-- BSP --
INSERT INTO mis_newbusiness
(branch, bsp, bsp_count, summary_date, captured)
SELECT branch, SUM (capital), COUNT (capital), SYSDATE, v_start_date
FROM zw30800p
WHERE brokercd NOT IN ('SBSA',
'KIOSKS',
'HMC',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND loanoff NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196')
AND SUBSTR (product, 1, 1) != 'S'
AND disdate BETWEEN v_start_date AND v_end_date
AND company NOT IN ('002')
GROUP BY branch;
----------------------------------------------------------------------------
----
-- CHECKING FOR DUPLICATES BEFORE COPYING DATA TO HISTORY TABLE ----------
----
----------------------------------------------------------------------------
----
v_cnt := 0;
-- Line 571 Error on next line -------------------------------------------
----
SELECT COUNT (*)
INTO v_cnt
FROM mis_wrkflw_pdincl_hist
WHERE TO_DATE (v_start_date, 'yyyy-mm-dd') =
TO_DATE (captured, 'yyyy-mm-
dd')
AND TO_DATE (SYSDATE, 'yyyy-mm-dd') =
TO_DATE (summary_date, 'yyyy-mm-
dd');
IF v_cnt = 0
THEN
INSERT INTO mis_newbus_hist
(branch, sbrokercd, sbsa, sbsa_count, bsp, bsp_count,
edcon,
edcon_count, staff_loans, staff_loans_count, call_centre,
call_center_count, payroll_exempt, pay_exempt_count,
payroll_compliant, pay_comp_count, hybrid_consultants,
hybrid_count, mobile_sales, mob_sales_count, esps,
esps_count, psa, psa_count, brokers, brokers_count,
eplan,
eplan_count, nupay, nupay_count, eplan_entry,
eplan_entry_count, nupay_entry, nupay_entry_count,
catchall,
catchall_count, captured, summary_date)
SELECT branch, sbrokercd, sbsa, sbsa_count, bsp, bsp_count, edcon,
edcon_count, staff_loans, staff_loans_count, call_centre,
call_center_count, payroll_exempt, pay_exempt_count,
payroll_compliant, pay_comp_count, hybrid_consultants,
hybrid_count, mobile_sales, mob_sales_count, esps,
esps_count,
psa, psa_count, brokers, brokers_count, eplan, eplan_count,
nupay, nupay_count, eplan_entry, eplan_entry_count,
nupay_entry, nupay_entry_count, catchall, catchall_count,
captured, summary_date
FROM mis_newbusiness;
END IF;
END;
|
|
|
Re: ORA-01843: not a valid month [message #18947 is a reply to message #18945] |
Fri, 22 February 2002 03:46 |
Suresh Vemulapalli
Messages: 624 Registered: August 2000
|
Senior Member |
|
|
q1) you dont need to convert sysdate using to_date.it is already date value.
AND trunc(SYSDATE) =
TO_DATE (summary_date, 'yyyy-mm-
dd');
q2) if u are not passing hours,mins then oracle adds 00:00:00 to date value.
|
|
|
Goto Forum:
Current Time: Sat Sep 28 16:55:47 CDT 2024
|