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
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.
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.
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”))