check for invalid dates

[mmwdmr]

I need to put an "exception" report together that looks for invalid dates. By invalid I mean, data entry errors that do not satisy a particular format, in this case: MM/DD/YYYY. Unfortunately, the database has no field validation of the date format so users can enter errors. Thanks!

 

[trezlub]

try using the isDate function. The function returns a boolean.

Something like this will help you:

If isDate(<someDate>) then

waffle...
waffle...
waffle...

end if

 

[mmwdmr]

I dont' see how this will help me find dates in an ivalid format. I'm trying to find data entry errors like 3/1/2 (instead of 3/1/02).

 

[trezlub]

validate the input data. It is the easiest way in the long run. You don't want users typing in any old rubbish. Be aware that if you let garbage in the chances are garbage will come back out. Validate the data!!!!!!!

 

[mmwdmr]

I've been a DBA for many years and you are really stating the obvious about validating data and the whole garbage in, garbage out thing. Also, I did not program this database and cannot change the code on date validation as it is a propietary program and the vendor does not release the code! So, I am stuck with users being able to type in &quot;rubbish.&quot; What I need is a solution so we can give the manager a report of ivalid dates that staff can go in and correct. Thanks.

 

[Ngolem]

let's first define what is an invalid date format

Are the fields involved date-type or string-type fields??

Are the valid fields of the form &quot;dd/mm/yy&quot; or is &quot;d/m/yy&quot; also valid?...&quot;dd/mm/yyyy&quot; valid too?

Are we just concerned about the form of the date (I am assuming string here) or also about the value?

eg. 03/13/2003 is obviously wrong for &quot;dd/mm/yyyy&quot;

but what about 03/04/2003? we must assume this is a correct date and the user did not mean 03 for the month instead of 04...or is there a away in the db to verify which month should be used? Eg...logical sequence of dates?

What are your rules for valid dates

Once that is established then we can certainly devise an exception report.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[lbass]

Something like this might work, but as Jim said, you need to provide the exact rules--here I made them up:

stringvar array baddatept := split(&quot;3/1/2&quot;,&quot;/&quot;

if length(baddatept[1]) <> 2 or
val(baddatept[1]) > 12 or
length(baddatept[2]) <> 2 or
val(baddatept[2]) > 31 or
length(baddatept[3]) <> 4 or
val(baddatept[3]) < 1950 or
val(baddatept[3]) > 2003 then
&quot;BadDateFormat&quot; else &quot;&quot;;

-LB

 

[mmwdmr]

These are date fields, not string. I am concerned with the form of the field, not the value. The database date fields will accept a number of entry options such as, 01/01/02, 1/1/02, 01/01/2002, 1/1/2002, etc. However it is keyed in, the program is supposed to turn it into MM/DD/YYYY as soon as the user hits enter and moves to the next field. The problem seems to be with the year - the database is not always turning it into YYYY format. I don't know if this a data corruption issue or what exactly is causing it but there are dates that are stored as 01/01/2. These are the ones I need to see on an exception report.

 

[synapsevampire]

You'll want to check for numerouis things, and while you're at it, a new developer for the application to put in validation.

I would start the formula with trezlub's suggestion, add in LB's, and then take it from there.

stringvar array baddatept := split({table.date},&quot;/&quot;
if not(isDate({table.date})) // quickly out if it's bad
or
(
len({table.date}) < 6 //quick sanity check
or
(len({table.date}) > 8
)
or
(
// the slower element verification if it passes the
// first tests
if length(baddatept[1]) <> 2 or
val(baddatept[1]) > 12 or
length(baddatept[2]) <> 2 or
val(baddatept[2]) > 31 or
length(baddatept[3]) <> 4 or
val(baddatept[3]) < 1950 or
val(baddatept[3]) > 2003 then
&quot;BadDateFormat&quot; else &quot;&quot;;
)

-k

 

[synapsevampire]

I see, try:

year({Table.date}) < 1970 // whatever year makes sense

-k

 

[mmwdmr]

LB - this logic is pretty cool. However, I have not used the &quot;stringvar array&quot; function before. Are there any other formulas to initialize or call the variable and/or the array statement? And what's the deal with &quot;split&quot;? I've not seen that before either!

 

[Ngolem]

Question?

If it is a valid date type in your database...why is it a problem?

I can make a valid date appear anyway I want in Crystal.

I really don't know how you could test for an invalid form.

Eg. I can take any valid date and express it in the form of

dd/mm/yyyy totext({table.date},&quot;dd/MM/yyyy&quot
dd/mm/yy totext({table.date},&quot;dd/MM/yy&quot

if it is in a valid Date format already I don't think we can test it's form...since we can manipulate it anyway we want....if it were a string or number datatype that would be a different manner since there are lots of tests to apply.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[mmwdmr]

The most simple solution is not always the most obvious. Since the issue is mainly with the year not always being stored in the DB as YYYY, the solution suggested by &quot;K&quot; actually worked, although it also picked up null date fields but I can deal with those...thanks everyone!!