asked on

14 days before loan start date in oracle sql

Hi

I have a field (QINST_VALUE) in my database which holds all sorts:

24/05/2016
Y
1000.00
yy
25/05/2015
23.00
24/05/2015
cghnfgn
National Rail
27/05/2016
300.50
27/05/2015
Y

I am working with the date fields only.

All i am trying to do is find anything 14 days before the QINST_VALUE (as START_OF_LOAN)

WHERE QINST_VALUE between sysdate -14 and sysdate

Open in new window

but im getting the error:

ORA-01840: input value not long enough for date format
01840. 00000 - "input value not long enough for date format"
*Cause:
*Action:

I have tried converting the date string into a DATE but its not working.

and QINST_VALUE < TO_CHAR(sysdate - 14, 'dd/mm/yyyy')

Open in new window

Any ideas?

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

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

Start Free Trial

Richiep86

ASKER

i can restrict the query so it only brings back the date...

with cte_header as (
select * from D1040M 
inner join D1046M on D1046M.QINST_QUES_REF  = D1040M.QUES_REF and   QUES_ID = 'EF0035'
left outer join D1189M on D1189M.QINST_REF = D1046M.QINST_REF
left outer join D1521M on D1521M.auth_form_number = form_number
inner join D1047M on D1047M.QINST_REF = D1046M.QINST_REF
inner join D1044M on D1047M.QINST_QQUES_REF = D1044M.QQUES_REF
inner join D1048M on  D1048M.QINST_REF = D1046M.QINST_REF
),

cte_lv
as
(
select 
e.PERSON_REF,
e.EMPLOYEE_NUMBER,
coalesce(rtrim(ltrim(known_as)),per.FIRST_FORNAME)  as FORENAME,
per.SURNAME as SURNAME,
per.NI_NO NINO,
per.INITIALS INITS,
per.TITLE TITLE,
post.number_R as POST_NUMBER,
post.long_desc as POST_LONG_DESC,
e.START_DATE EMPSTARTDATE,
e.END_DATE EMPENDDATE,
CGrp.ID CGRPID,
CGrp.LONG_DESC CGRPDESC,
PGrp.ID PGRPID,
PGrp.LONG_DESC PGRPDESC,
narr_occtype.long_desc as OCC_TYPE,
narr_joinreason.long_desc as POST_JOIN_REASON,
narr_joinreason2.long_desc as EMP_JOIN_REASON,
ph.projected_end_date
from D550M e
left outer join D500M per on e.PERSON_REF = per.PERSON_REF
left outer join D100M CGrp on e.LEVEL1_PAY_STR_REF = CGrp.REF
left outer join D100M PGrp on e.PAY_STR_REF = PGrp.REF 
left outer join D580M ph on e.PERSON_REF = ph.PERSON_REF and
( ph.start_date <= sysdate and (ph.end_date is null or ph.end_Date >= sysdate))
left outer join D200M post on ph.ref  = post.ref
left outer join D800M narr_occtype on ph.occ_type = narr_occtype.narrative_code and narr_occtype.narrative_category = 'OCCTYP'
left outer join D800M narr_joinreason on ph.join_reason= narr_joinreason.narrative_code and narr_joinreason.narrative_category = 'RSNCHG'
left outer join D800M narr_joinreason2 on e.orig_how_joined = narr_joinreason2.narrative_code and narr_joinreason2.narrative_category = 'RSNCHG'
)
select * from (select distinct

EMPLOYEE_NUMBER,
FORENAME,
SURNAME,
--NINO,
--INITS,
--TITLE,
--POST_NUMBER,
--POST_LONG_DESC,
--EMPSTARTDATE,
--EMPENDDATE,
--CGRPID,
--CGRPDESC,
--PGRPID,
--PGRPDESC,
--OCC_TYPE,
--POST_JOIN_REASON,
--EMP_JOIN_REASON,
--projected_end_date,
--QUES_ID,
--QUES_TITLE,
--AUTH_TYPE,
--AUTH_KEY,
--AUTH_ACTION_TYPE,
--AUTH_ACTION,
--AUTH_ACTION_DATE,
--AUTH_ACTION_TIME,                
QINST_VALUE as START_OF_LOAN 
--qques_id

--qques_id
from cte_header

inner join cte_lv on cte_header.Person_ref = cte_lv.person_ref

where qques_id = 'EF0035Q3'
and auth_action like ('AUTH%')
and  AUTH_ACTION_DATE between sysdate -7 and sysdate
--QINST_VALUE < TO_CHAR(sysdate - 14, 'dd/mm/yyyy') 


)

Open in new window

where qques_id = 'EF0035Q3' will only return dates... is that ok? Can i now search for 14 days before the loan date?

johnsone

That should work. The error you are receiving is due to a type implicit type conversion of the character field to a date.

awking00

A number of questions arise from your description so far.
>>where QQUES_ID = 'EF0035Q3' will only return dates<<
Does that mean all the QINST_VALUEs would be character strings in the form of 'dd/mm/yyyy'?
Is AUTH_ACTION always upper case?
Is AUTH_ACTION_DATE a true date datatype (with or without a time portion)?
>>QINST_VALUE < TO_CHAR(sysdate - 14, 'dd/mm/yyyy')<<
This could be problematic as it will make comparisons based on ascii values - 18/05/2015 will be less than 25/04/2015 and, conversely, 25/04/2015 will be greater than 18/05/2015.