OraFAQ Forum: SQL & PL/SQL » ORA-01843: not a valid month

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01843: not a valid month

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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;

Report message to a moderator

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.

Report message to a moderator

Previous Topic:	Select records within a range from a BIG table
Next Topic:	sql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 08:58:07 CDT 2024