Invalid Dates

[Former Member]

HI

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!

[former_member260594 · Accepted solution]

Hello achett,

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

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

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

Thanks!

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

Try something like this...

IF Len(ToText({TableName.DateField},"")) <> 8 THEN False
ELSE IF IsDate({TableName.DateField}) THEN True ELSE False

HTH,

Jason

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

Let you know how it goes.

[Former Member · Accepted solution]

// 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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

So just change this line??

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

to

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

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

[Former Member · Accepted solution]

YES.

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

[Former Member · Accepted solution]

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!

[Former Member · Accepted solution]

Have you tried debugging the first ten dates that look valid to see if it is really 8 characters long? I ran just those dates through the formula and they all come out as being valid dates, which makes me think some other condition is in play here.

Edited by: Sanjay Kodidine on May 7, 2010 11:22 AM

[Former Member · Accepted solution]

Sanjay,

Sorry couldn't post earlier due to physical sickness. This formula works great!!!! Thank you so much for all your help.

If you dont mind could you please explain the leap year part of the formula?

Appreciate it!!

Edited by: achett13 on May 14, 2010 3:49 AM