Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01840: input value not long enough for date format (ORACLE 11g)
| ORA-01840: input value not long enough for date format [message #615854] |
Tue, 10 June 2014 04:42  |
 |
jayant.tripathi
Messages: 8
Registered: October 2013
|
Junior Member |
|
|
Hi
I am getting "ORA-01840: input value not long enough for date format" error while executing the below SQL. This error is occurring only for last date of each month.
The SQL is:
INSERT INTO tmp_ndd_nddraw_fms_data
(spid, nodename, gdate, gtime, gdate_time, ndd_mw, ndd_mv, fms_mw,
fms_mv, nddraw_mw, nddraw_mv)
SELECT DISTINCT :SPID, t1.nodename nodename, t1.gdate, t1.gtime,
t1.gdate_time, t1.ndd_mw, t1.ndd_mv, nvl(t1.fms_mw,0)+t2.fms_mw+mw, nvl(t1.fms_mv,0)+t2.fms_mv+mv,
t1.nddraw_mw, t1.nddraw_mv
FROM tmp_ndd_nddraw_fms_data t1, tmp_ndd_nddraw_fms_data t2, (
WITH min_max_date as(select min(gdate) min_date,max(gdate) max_date from tmp_ndd_nddraw_fms_data),
maps as( select gen_name from unmetered_gsp_wind_map where gspid=:SPID),
t AS
(
SELECT TO_CHAR (f.target_datetime, 'YYYYmmdd') gdate,
TO_CHAR (f.target_datetime, 'HH24mi') gtime,
SUM (f.mean_frcstd_mw) mw
FROM picked_data f, wind_generator w,maps m,min_max_date d
WHERE f.gen_id = w.gen_id
AND w.gen_name =m.gen_name
AND f.target_datetime BETWEEN TO_DATE (d.min_date,
'yyyymmddhh24mi'
)
- 1 / 48
AND TO_DATE (d.max_date,
'yyyymmddhh24mi'
)
+ 49 / 48
GROUP BY f.target_datetime
ORDER BY TO_CHAR (f.target_datetime, 'YYYYmmdd'),
TO_CHAR (f.target_datetime, 'HH24mi')),
wind_mw AS
(
SELECT gdate,
DECODE (gtime,
2330, 2400,
TO_CHAR ( TO_DATE (LPAD (gtime, 4, '000'), 'HH24mi')
+ 1 / 48,
'HH24MI'
)
) gtime,
mw
FROM (SELECT gdate, gtime + 0 gtime, mw
FROM t t1
UNION
SELECT t1.gdate, t1.gtime + 30 gtime, (t1.mw + t2.mw) / 2
FROM t t2, t t1
WHERE (t1.gdate = t2.gdate AND t1.gtime + 100 = t2.gtime)
OR ( t1.gdate + 1 = t2.gdate
AND t1.gtime = 2300
AND t2.gtime = 0
)),min_max_date
WHERE gdate BETWEEN min_date AND max_date),
t2 AS
(
SELECT TO_CHAR (f.target_datetime, 'YYYYmmdd') gdate,
TO_CHAR (f.target_datetime, 'HH24mi') gtime,
SUM (f.f_mvar) mw
FROM ext_mvar_data f, wind_generator w,maps m,min_max_date d
WHERE f.gen_id = w.gen_id
AND w.gen_name =m.gen_name
AND f.target_datetime BETWEEN TO_DATE (min_date,
'yyyymmddhh24mi'
)
- 1 / 48
AND TO_DATE (d.max_date ,
'yyyymmddhh24mi'
)
+ 49 / 48
GROUP BY f.target_datetime
ORDER BY TO_CHAR (f.target_datetime, 'YYYYmmdd'),
TO_CHAR (f.target_datetime, 'HH24mi')),
wind_mv AS
(
SELECT gdate,
DECODE (gtime,
2330, 2400,
TO_CHAR ( TO_DATE (LPAD (gtime, 4, '000'), 'HH24mi')
+ 1 / 48,
'HH24MI'
)
) gtime,
mw mv
FROM (SELECT gdate, gtime + 0 gtime, mw
FROM t2 t1
UNION
SELECT t1.gdate, t1.gtime + 30 gtime, (t1.mw + t2.mw) / 2
FROM t2 t2, t2 t1
WHERE (t1.gdate = t2.gdate AND t1.gtime + 100 = t2.gtime)
OR ( t1.gdate + 1 = t2.gdate
AND t1.gtime = 2300
AND t2.gtime = 0
)),min_max_date
WHERE gdate BETWEEN min_date AND max_date)
(SELECT wind_mw.gdate, wind_mw.gtime, nvl(mw,0)mw, nvl(mv,0) mv
FROM wind_mw, wind_mv
WHERE wind_mw.gdate = wind_mv.gdate(+) AND wind_mw.gtime = wind_mv.gtime(+))) T3
WHERE t1.gdate = t2.gdate
AND t1.gtime = t2.gtime
and t1.gtime=t3.gtime
and t1.gdate=t3.gdate
AND t1.nodename = t2.nodename || 'DW'
AND t1.nodename LIKE '%DW'
AND t1.spid = 0
The cloumn "target_datetime" of picked_data and ext_mvar_data tables is of type Date.
Coudl you please help me in resolving this issue as this is causing lots of trouble.
Thanks.
|
|
|
|
|
|
|
|
|
|
| Re: ORA-01840: input value not long enough for date format [message #615860 is a reply to message #615858] |
Tue, 10 June 2014 05:44  |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
I will also say that that error only comes from a call to to_date, you only have 3 such calls (well 6 but the 2nd three are repeats of the 1st three), so really you should be able to look at the source data for those 3 and work out which one is in the wrong format, and that's not something we can do for you.
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Aug 02 21:22:31 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
