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?
Richiep86Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
How can you compare a value of "National Rail" to a date?  The database is attempting to convert that to a date and do a comparison.

I would think that you need to restrict your query to things that look like dates.  Probably something like this:

select ... from (select ... from ... where regexp_like (qinst_value, '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]')) tab1
where to_date(tab1.qinst_value, 'dd/mm/yyyy') between sysdate-14 and sysdate;

I'm not great with regular expressions, but I think that should work.

If you have something in that column that matches the patter but is not a valid date, then you are going to have a problem.  You would probably have to write some sort of custom function to validate the date.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richiep86Author Commented:
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?
johnsoneSenior Oracle DBACommented:
That should work.  The error you are receiving is due to a type implicit type conversion of the character field to a date.
awking00Information Technology SpecialistCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.