Finding bad dates in a text field

Panorama "Classic"
1

I’m looking for an idea on how to easily find and eliminate bad dates in a text field.

The field is set to text and good dates are in either mm/dd/yy or mm/dd/yyyy. There’s a lot I can do to prevent them in the first place, but I’m looking for better ideas than I’ve had on my own of how to select those that are entered instead as // or Joe or 12345.

All of my “best” ideas run into an illegal date error.

I need something that equates Select date(TheField) = -1

2

Well, in Panorama X you could use

select catcherror(0,date(TheField))=0

But you put this in the “Classic” category so that’s not going to help you now (I’m guessing this is for the a server application).

3

This is just a brute force, mainly untested possible solution:

    SelectReverse```

The first 2 criteria check that the month is between 1 and 12. The next two check to see that the day is between 1 and 31. Finally it checks to see if the last two digits of the year contain any two digits. Obviously this could miss some bazaar entries like 5k/12/year2014 but it should get the lion's share of bad dates.
4

Yes, it is Classic and a web app.

Ideally no bad dates should get there but one snuck in the other day, leading to a 500 Error. I found the apparent hole; javascript was turned off in the browser which bypassed the client side check.

I’ve now added appropriate checks on the server side, which should’ve been there anyway. For safety sake though, I’m looking for a routine that can eliminate any others if they get through. Once they’re there, they’re hard to get rid of.

5

Thanks Gary, that’s along the lines of what I was afraid it might take. I was hoping for a flick of the wrist versus a pounding with a fist. :laughing:

I’ll give it a go since that’s apparently the sole option.

6

You could add conditions for arraysize(TheField,"/")=3 and replace(TheField,"/","")=striptonum(TheField)

7

The flick of the wrist!!

8

Thanks for the ideas Gary and Dave. Here’s what seems to be doing the job and allows for five years in advance, with TheField changed to the actual field name: Date.

Select stripchar(Date,“09//”) = Date and arraysize(Date,“/”)= 3
and val(array(Date,1,“/”)) > 0 and val(array(Date,1,“/”)) < 13
and val(array(Date,2,“/”)) > 0 and val(array(Date,2,“/”)) < 32
and val(array(Date,3,“/”)[-2,-1]) > 15
and val(array(Date,3,“/”)[-2,-1]) < val(datepattern(today()+365*5,“yy”))