Checking data in date field is date?

Discuss your general Proclaim related queries here.
Tardisgx
Posts: 15
Joined: Wed Jun 19, 2019 10:35 am
Has thanked: 3 times
Been thanked: 1 time

Checking data in date field is date?

Post by Tardisgx »

I have encountered 2 issues importing date fields
1) Excel is American so tries to default to mm/dd/yyyy even after you close the csv or sometimes a blank date appears 00/01/1900 (this is relevant)
2) Human Error also allows non date information to be imported into the field

This causes reports to error (58 in the log) or to output half finished which is the issue.

Right now this has happened somewhere in the last 2 months among 3 claim types there is non date information in a date field

I cannot be certain which field it is as running an SQL query or task server log does not give that specific information. I think it is the accident date.

Can I create a maths field that converts the date to text then splits up the date by each slash then asks
if blank show blank
Is the day less than 31 greater than 0
Is the month less than 13 greater than 0
If it is ok say yes that’s a date
Or if an error in the attempt to do the maths occurs output error

I tried to get the SQL query to do something similar with the criteria (date.year<>2020) but the non date data could not be processed with this criteria on and it error out.

I don’t want to begin to try to write the above only to encounter an error I can’t overcome. Like with the sql query.

danny
Posts: 61
Joined: Sun Mar 11, 2012 11:28 pm
Has thanked: 3 times
Been thanked: 4 times

Re: Checking data in date field is date?

Post by danny »

alright
eclipse have a tool/query to check for invalid dates they can run on your db just ask support

revellbikes
Posts: 467
Joined: Fri Jun 15, 2012 12:44 pm
Has thanked: 15 times
Been thanked: 48 times

Re: Checking data in date field is date?

Post by revellbikes »

And I'd also look to review all import routines to identify any date fields that are being imported directly into your database. They should be imported into temporary alpha fields, checks performed on formatting and moved to correct field if validation passed.

Tardisgx
Posts: 15
Joined: Wed Jun 19, 2019 10:35 am
Has thanked: 3 times
Been thanked: 1 time

Re: Checking data in date field is date?

Post by Tardisgx »

revellbikes wrote:
Thu Sep 17, 2020 9:24 am
And I'd also look to review all import routines to identify any date fields that are being imported directly into your database. They should be imported into temporary alpha fields, checks performed on formatting and moved to correct field if validation passed.
Yes this is how it should be. It is an undertaking. But this is the best long term solution.

Tardisgx
Posts: 15
Joined: Wed Jun 19, 2019 10:35 am
Has thanked: 3 times
Been thanked: 1 time

Re: Checking data in date field is date?

Post by Tardisgx »

revellbikes wrote:
Thu Sep 17, 2020 9:24 am
And I'd also look to review all import routines to identify any date fields that are being imported directly into your database. They should be imported into temporary alpha fields, checks performed on formatting and moved to correct field if validation passed.
I have written the maths field. I hope others find this comment so they don't need to write it.

I tried to use listfind but could not get it working.

Code: Select all

v-DEBUG = "" 
slashone = SPLIT( {Date Alpha Validate.Text} , 3 , 1 ) 
slashtwo = SPLIT( {Date Alpha Validate.Text} , 6 , 1 ) 
dayx = SPLIT( {Date Alpha Validate.Text} , 1 , 2 ) 
monthx = SPLIT( {Date Alpha Validate.Text} , 4 , 2 ) 
yearx = SPLIT( {Date Alpha Validate.Text} , 7 , 4 ) 
yearstart = SPLIT( {Date Alpha Validate.Text} , 7 , 2 ) 
lengthx = LENGTH( {Date Alpha Validate.Text} ) 
IF yearx CONTAINS "#" OR yearx CONTAINS "/" OR yearx CONTAINS "A" OR yearx CONTAINS "a" OR yearx CONTAINS "`" OR yearx CONTAINS "¬" OR yearx CONTAINS "B" OR yearx CONTAINS "b" OR yearx CONTAINS "-" OR yearx CONTAINS "!" OR yearx CONTAINS "C" OR yearx CONTAINS "c" OR yearx CONTAINS "=" OR yearx CONTAINS "" OR yearx CONTAINS "D" OR yearx CONTAINS "d" OR yearx CONTAINS "\" OR yearx CONTAINS "£" OR yearx CONTAINS "E" OR yearx CONTAINS "e" OR yearx CONTAINS "[" OR yearx CONTAINS "$" OR yearx CONTAINS "F" OR yearx CONTAINS "f" OR yearx CONTAINS "]" OR yearx CONTAINS "%" OR yearx CONTAINS "G" OR yearx CONTAINS "g" OR yearx CONTAINS ";" OR yearx CONTAINS "^" OR yearx CONTAINS "H" OR yearx CONTAINS "h" OR yearx CONTAINS "'" OR yearx CONTAINS "&" OR yearx CONTAINS "I" OR yearx CONTAINS "i" OR yearx CONTAINS "" OR yearx CONTAINS "*" OR yearx CONTAINS "J" OR yearx CONTAINS "j" OR yearx CONTAINS "," OR yearx CONTAINS "(" OR yearx CONTAINS "K" OR yearx CONTAINS "k" OR yearx CONTAINS "." OR yearx CONTAINS ")" OR yearx CONTAINS "L" OR yearx CONTAINS "l" OR yearx CONTAINS "_" OR yearx CONTAINS "M" OR yearx CONTAINS "m" OR yearx CONTAINS "+" OR yearx CONTAINS "N" OR yearx CONTAINS "n" OR yearx CONTAINS "*" OR yearx CONTAINS "O" OR yearx CONTAINS "o" OR yearx CONTAINS "-" OR yearx CONTAINS "P" OR yearx CONTAINS "p" OR yearx CONTAINS "+" OR yearx CONTAINS ":" OR yearx CONTAINS "Q" OR yearx CONTAINS "q" OR yearx CONTAINS "." OR yearx CONTAINS "@" OR yearx CONTAINS "R" OR yearx CONTAINS "r" OR yearx CONTAINS "~" OR yearx CONTAINS "S" OR yearx CONTAINS "s" OR yearx CONTAINS "<" OR yearx CONTAINS "T" OR yearx CONTAINS "t" OR yearx CONTAINS "|" OR yearx CONTAINS "U" OR yearx CONTAINS "u" OR yearx CONTAINS ">" OR yearx CONTAINS "V" OR yearx CONTAINS "v" OR yearx CONTAINS "?" OR yearx CONTAINS "W" OR yearx CONTAINS "w" OR yearx CONTAINS "X" OR yearx CONTAINS "x" OR yearx CONTAINS "Y" OR yearx CONTAINS "y" OR yearx CONTAINS "Z" OR yearx CONTAINS "z" THEN 
    invalid = "Invalids Present"  
ELSE 
    invalid = "No Invalids" 
END 
# UNABLE TO ACCOUNT FOR DOUBLE QUOTE OR SQUIGGLY BRACKETS {} 
IF yearstart = "19" OR "20" THEN 
    yearroo = "Correct  Yearstart"  
ELSE 
    yearroo = "Incorrect Yearstart" 
END 
IF yearx = "1900" THEN 
    yearrootwo = "Yes 1900"  
ELSE 
    yearrootwo = "No 1900" 
END 
IF slashone = "/" AND slashtwo = "/" THEN 
    slashes = "Correct Slashes"  
ELSE 
    slashes = "Incorrect Slashes" 
END 
IF lengthx = 10 THEN 
    lengthroo = "Correct Length"  
ELSE 
    lengthroo = "Incorrect Length" 
END 
IF dayx = "01" OR dayx = "02" OR dayx = "03" OR dayx = "04" OR dayx = "05" OR dayx = "06" OR dayx = "07" OR dayx = "08" OR dayx = "09" OR dayx = "10" OR dayx = "11" OR dayx = "12" OR dayx = "13" OR dayx = "14" OR dayx = "15" OR dayx = "16" OR dayx = "17" OR dayx = "18" OR dayx = "19" OR dayx = "20" OR dayx = "21" OR dayx = "22" OR dayx = "23" OR dayx = "24" OR dayx = "25" OR dayx = "26" OR dayx = "27" OR dayx = "28" OR dayx = "29" OR dayx = "30" OR dayx = "31" THEN 
    dayroo = "Correct Day"  
ELSE 
    dayroo = "Incorrect Day" 
END 
IF monthx = "01" OR monthx = "02" OR monthx = "03" OR monthx = "04" OR monthx = "05" OR monthx = "06" OR monthx = "07" OR monthx = "08" OR monthx = "09" OR monthx = "10" OR monthx = "11" OR monthx = "12" THEN 
    monthroo = "Correct Month"  
ELSE 
    monthroo = "Incorrect Month" 
END 
IF invalid = "No Invalids" AND slashes = "Correct Slashes" AND lengthroo = "Correct Length" AND dayroo = "Correct Day" AND monthroo = "Correct Month" AND yearroo = "Correct  Yearstart" AND yearrootwo = "No 1900" THEN 
    RESULT = "Date"  
ELSE 
    RESULT = "Not Date" 
END

danny
Posts: 61
Joined: Sun Mar 11, 2012 11:28 pm
Has thanked: 3 times
Been thanked: 4 times

Re: Checking data in date field is date?

Post by danny »

or by odbc sanity check to return list of invalid date fields

Code: Select all

select 
	a.case_no, 
	b.field_desc, 
	b.field_id,
	a.field_value,
	length(a.field_value) 
from 
	pub.case_field a 
inner join 
	pub.field_type b 
on 
	b.field_id=a.field_id
where 
	b.field_type = 7 and 
	( (a.field_value not like '1%' and a.field_value not like '2%') or length(a.field_value) != 8 )
probably not optimised for indexes but does job

danny
Posts: 61
Joined: Sun Mar 11, 2012 11:28 pm
Has thanked: 3 times
Been thanked: 4 times

Re: Checking data in date field is date?

Post by danny »

or ask eclipse to run to run their SupportTool.r with "Display Invalid Dates" routine