Question Re: Invalid Dates in Technology Q&A

Invalid Dates

Former Member — Wed, 05 May 2010 20:02:45 GMT

I'm trying to run a report with all the invalid dates that are in the system.

Dates are stored as Numeric Values and are in this format "20100505" ( May 5th 2010) but there are a whole lot of invalid dates that shouldn't be in the system but somehow and someone finds a way to put them in.

How can I find these invalid dates that do not have the "20100505" format or valid year/month/day???

Examples of invalid dates are

220022222

40002348

23330

458881

Any help is appreciated!

Thanks!

Re: Invalid Dates

former_member260594 — Wed, 05 May 2010 21:23:53 GMT

Hello achett,

Try using the isdate function to check whether it is a valid date. It will return true or false.

Re: Invalid Dates

Former Member — Wed, 05 May 2010 23:47:44 GMT

I don't think IsDate will work directly in this scenario, it might need formatting prior to use.

I would suggest code such as this in your selection formula to pull only records which are not a valid date, assuming that all records should have the date as 8 characters.


not(
length(totext(x,0,'','')) = 8 and 
isdate(
  totext(
     date(
       tonumber(totext(x,0,'','')[1 to 4]), 
       tonumber(totext(x,0,'','')[5 to 6]), 
       tonumber(totext(x,0,'','')[7 to 8])),"MMMM dd, yyyy")))

where X is the numeric date field.

Re: Invalid Dates

Former Member — Thu, 06 May 2010 14:51:35 GMT

The problem is there are values that are less and more than 8 digits.

Thanks!

Re: Invalid Dates

Former Member — Thu, 06 May 2010 16:09:02 GMT

Ignore my post, not sure what I was thinking (if at all), Graham is correct, you can simply use IsDate to see if it will translate to a valid date. So in your selection criteria, just use:


IsDate({numeric.datefield}) = false

Edited by: Sanjay Kodidine on May 6, 2010 8:39 AM

Re: Invalid Dates

Former Member — Thu, 06 May 2010 16:47:06 GMT

Thanks Sanjay..

But if the lenght of X is not 8 lets says it's 5 (eg 50833) how will it know the position starting 6 to 8?? The report kicks off saying tonumber(totext(X,0,'','')[7 to 8]) is non-numeric.

Any idea.

Re: Invalid Dates

Former Member — Thu, 06 May 2010 17:03:30 GMT

Try something like this...


IF Len(ToText({TableName.DateField},"")) &lt;&gt; 8 THEN False
ELSE IF IsDate({TableName.DateField}) THEN True ELSE False

HTH,

Jason

Re: Invalid Dates

Former Member — Thu, 06 May 2010 17:24:33 GMT

I do apologize for the flip flop on the formula, it has been a long day already, and it is only 9 a.m.

I recall why I said Graham's (and now Jason's) formula will not work. It is because of the way IsDate works. If you put in a number such as 13 which obviously is not a valid date, IsDate adds it as number of days since 12/30/1899 and then checks to see if that is a valid date.

Here is my suggestion for the selection criteria:


not(
length(totext(X,0,'','')) = 8 
and tonumber(totext(X,0,'','')[5 to 6]) in 1 to 12
and tonumber(totext(X,0,'','')[7 to 8]) in 1 to 31
and (if tonumber(totext(X,0,'','')[5 to 6]) = 2 then
(
if (tonumber(totext(X,0,'','')[1 to 4]) mod 400 = 0) or 
( (tonumber(totext(X,0,'','')[1 to 4]) mod 4 = 0) and
 (tonumber(totext(X,0,'','')[1 to 4]) mod 100 <> 0) )
then tonumber(totext(X,0,'','')[7 to 8]) = 29
else tonumber(totext(X,0,'','')[7 to 8]) < 29
)
else (if tonumber(totext(X,0,'','')[5 to 6]) in [1, 3, 5, 7, 8, 10, 12] then
tonumber(totext(X,0,'','')[7 to 8]) < 32
else if tonumber(totext(X,0,'','')[5 to 6]) in [4, 6, 9, 11] then
tonumber(totext(X,0,'','')[7 to 8]) < 31
))
and
isdate(
  totext(
     date(
       tonumber(totext(X,0,'','')[1 to 4]), 
       tonumber(totext(X,0,'','')[5 to 6]), 
       tonumber(totext(X,0,'','')[7 to 8])),"MMMM dd, yyyy"))
)

This should return all values that are less than or greater than 8 characters and also those values that are 8 characters but not a valid date. It also takes into consideration leap years.

Edited by: Sanjay Kodidine on May 6, 2010 10:08 AM

Re: Invalid Dates

Former Member — Thu, 06 May 2010 21:13:24 GMT

Yes I did try Jason's formula but it did not work...I'm trying something else that I used in MS Access to get the same results.

This is what I used in MS Access...

Date1: IIf(Len([date])=8,(IIf(IsDate(Mid([date],5,2) & "/" & Right([date],2) & "/" & Left([date],4)),[date],"19800101")),"19800101")

This formula looks for any value with Len=8 and inserts "19800101" as a default value.

I'm currently trying to modify this to make it work in Crystal...

I'll take a shot at the formula you provided...let you know how it goes.

Thanks all!

Edited by: achett13 on May 6, 2010 11:16 PM

Re: Invalid Dates

Former Member — Fri, 07 May 2010 13:43:16 GMT

This section is trying to avoid any leap year errors.

if (tonumber(totext(X,0,'','')[1 to 4]) mod 400 = 0) or

( (tonumber(totext(X,0,'','')[1 to 4]) mod 4 = 0) and

(tonumber(totext(X,0,'','')[1 to 4]) mod 100 0) )

then tonumber(totext(X,0,'','')[7 to 8]) = 29

I have appointment dates where 20040229 and 20080229 (Leap year) shows up on my report though they are valid dates.

All 2000 feb dates seem to show up on the report as well though they are valid.

I'm not too familiar with mod functions.

Could you please explain me this part of the formula?

Thanks!

Edited by: achett13 on May 7, 2010 4:40 PM

Re: Invalid Dates

Former Member — Fri, 07 May 2010 15:45:16 GMT

That is strange, I tested it with 20040229 and 20080229 and they both come out as valid dates (i.e. they do not show up in the report)..


>  (tonumber(totext(X,0,'','')[1 to 4]) mod 100  0) )
>

For some reason the post does not show "not equal to" sign. That line should read:

(tonumber(totext(X,0,'','')[1 to 4]) mod 100 NOT EQUAL TO 0) )

use the greater than and less than symbols as crystal does not recognize "not equal to".

If it is still an issue, post the complete formula that you have as well as the correct dates that are showing up.

Edited by: Sanjay Kodidine on May 7, 2010 7:45 AM

Re: Invalid Dates

Former Member — Fri, 07 May 2010 16:36:25 GMT

not(

length(totext({_MTMTRP.TAPTDT},0,'','')) = 8

and tonumber(totext({_MTMTRP.TAPTDT},0,'','')[5 to 6]) in 1 to 12

and tonumber(totext({_MTMTRP.TAPTDT},0,'','')[7 to 8]) in 1 to 31

and (if tonumber(totext({_MTMTRP.TAPTDT},0,'','')[5 to 6]) = 2 then

(

if (tonumber(totext({_MTMTRP.TAPTDT},0,'','')[1 to 4]) mod 400 = 0) or

((tonumber(totext({_MTMTRP.TAPTDT},0,'','')[1 to 4]) mod 4 = 0) and

(tonumber(totext({_MTMTRP.TAPTDT},0,'','')[1 to 4]) mod 100 <> 0))

then tonumber(totext({_MTMTRP.TAPTDT},0,'','')[7 to 8]) = 29

else tonumber(totext({_MTMTRP.TAPTDT},0,'','')[7 to 8]) < 29

)

else (if tonumber(totext({_MTMTRP.TAPTDT},0,'','')[5 to 6]) in [1, 3, 5, 7, 8, 10, 12] then

tonumber(totext({_MTMTRP.TAPTDT},0,'','')[7 to 8]) < 32

else if tonumber(totext({_MTMTRP.TAPTDT},0,'','')[5 to 6]) in [4, 6, 9, 11] then

tonumber(totext({_MTMTRP.TAPTDT},0,'','')[7 to 8]) < 31

))

and

isdate(

totext(

date(

tonumber(totext({_MTMTRP.TAPTDT},0,'','')[1 to 4]),

tonumber(totext({_MTMTRP.TAPTDT},0,'','')[5 to 6]),

tonumber(totext({_MTMTRP.TAPTDT},0,'','')[7 to 8])),"MMMM dd, yyyy"))

)

This is what I have...and since I changed the sign to <>(not equal to) it's pulling all the leap year appt dates for the whole month of FEB.

Thanks!

achett13

Re: Invalid Dates

Former Member — Fri, 07 May 2010 17:27:54 GMT

Aargh!!! I manually went through a few thousand records and it was a "doh" moment.

Equating it to 29 will error out all other Feb dates in a leap year, changing it to less then 30 should validate them properly.


// original line
then tonumber(totext(X,0,'','')[7 to 8]) < 30
// change to
then tonumber(totext(X,0,'','')[7 to 8]) < 30

Edited by: Sanjay Kodidine on May 7, 2010 9:32 AM

Re: Invalid Dates

Former Member — Fri, 07 May 2010 17:33:32 GMT

Let me try this...Thank you for all your help!!

Let you know how it goes.

Re: Invalid Dates

Former Member — Fri, 07 May 2010 17:34:34 GMT


// original line
then tonumber(totext(X,0,'','')[7 to 8]) = 29
// change to
then tonumber(totext(X,0,'','')[7 to 8]) < 30

Edited by: Sanjay Kodidine on May 7, 2010 9:34 AM

Re: Invalid Dates

Former Member — Fri, 07 May 2010 17:35:24 GMT

Nope...still doing the same thing. Populating the report with all the FEB dates

Re: Invalid Dates

Former Member — Fri, 07 May 2010 17:36:57 GMT

Post the dates it is displaying in the report along with the complete selection formula you have.

Re: Invalid Dates

Former Member — Fri, 07 May 2010 17:39:23 GMT

So just change this line??

then tonumber(totext({_MTMTRP.TAPTDT},0,'','')7 to 😎 = 29

then tonumber(totext({_MTMTRP.TAPTDT},0,'','')7 to 😎 <30

Edited by: achett13 on May 7, 2010 7:39 PM

Re: Invalid Dates

Former Member — Fri, 07 May 2010 17:39:52 GMT

YES.

Edited by: Sanjay Kodidine on May 7, 2010 9:40 AM

Re: Invalid Dates

Former Member — Fri, 07 May 2010 17:45:16 GMT

Here are the date

Appt Date:

20000210

20000215

20000222

20040219

20040216

20080212

20080215

20040219

20000207

222100

123655

22144

7758

and so forth..

Selection Criteria is

Table.TRPFLG}<> "WR" and

{Table.TEMPUP}<>"AS/400" and

{@Date Formula} = True

Thanks!