Convert string field to date 1

[steve053]

CR 10 and SQL 2000

We have a free text field to input a date that a surgical procedure was performed. No entry is permissible, as well as non-numeric text and symbols. I know that some of the values will never lend themselves to a conversion, but I would hope that a large percentage would.

Here is some sample data from the{Surgical_HX.Surgical_HX_Date} field and their expected output:

Surgical_HX_Date Output
00 1/1/2000
'06 1/1/2006
97? 1/1/1997
1997 1/1/1997
1997,1993 1/1/1997
1993,1999, 2003 1/1/2003
1997,1998,2001,2004 1/1/2004
1998 ? 1/1/1998
2000 approx 1/1/2000
2001 or 2002 1/1/2001
1999 1/1/1999
~1997 1/1/1997
1980s 1/1/1980
2001-02 1/1/2001
About'01 1/1/2001
'91, '93, '97 1/1/1997
94, 95, 99 1/1/1999

1-06 1/1/2006
3/02 3/1/2002
04/97 4/1/1997
5/1996 5/1/1996
04/2002 4/1/2002
1/93, 9/96 9/1/1996
Feb. 2003 2/1/2003
Sept 04 9/1/2004
Sept. 2004 9/1/2004
normal 11/98 11/1/1998

11/4/96 11/4/1996
4/21/2000 4/21/2000
10-21-96 10/21/1996
4/1/2003 4/1/2003
4/1987-3/1991-6/1993-10/1998 10/1/1998
9/10/93 Dr. Carron 9/10/1993

age 61 no output
LATE 40S no output
15-20 years ago no output
? no output

The above are the most frequent enrtry "styles", but it is not all inclusive.

If no specific day is listed the output should default to the first of the month (if given) or first of the year. If the date cannot be calculated, then a dummy future date could be inserted (or no data) for a manual review.

Thank you for your help.

 

[synapsevampire]

The first step should be to fire the coders and the dba for allowing such hogwash to be captured.

Addressing this in a report is kind of silly though, as the problem will still exist, and grow even worse.

I would convert the field to a date using a series of conditionals in a Stored Procedure, and change the front end capture routine to only allow valid date structures.

That said, the essence of what you want is a huge series of conditionals (IFs or CASEs), as in:

whileprintingrecords;
stringvar Dt:=trim({Table.datefield});
datevar MyDate;
if isdate(Dt) then
MyDate:=cdate(Dt)
else
if isnumeric(Dt) then
(
if len(Dt) in [2,4] then
MyDate:=cdate(val(Dt),1,1)
else
...

As you can see, the conditionals will be numerous, I've just scratched the surface.

-k

 

[steve053]

synapsevampire-

Thanks for the quick response and the base formula. Very much appreciated.

The first step should be to fire the coders and the dba for allowing such hogwash to be captured.

Addressing this in a report is kind of silly though, as the problem will still exist, and grow even worse.

LOL - I'd like to shoot the users more than the coders. It's the users who are driving what the coders and dba are allowed to do. In this application the users are MD's and clinicians - and they are pretty adamant about not being required to enter an exact date.

That being said, I have no choice but to convert the data from string to date.

Thank you again.

 

[synapsevampire]

I'm familiar with workinjg with MDs, they tend to think that they're good at many things they are far from competent in.

Consider creating another table and doing a batch conversion into it on occasion with a ID from the current table. That way in the future you can just convert that which hasn't already been converted.

It will be faster on the database side, and can be automated to run.

Lastly, I would write a strong memo to the IT director and the board requesting that the MDs and their ilk be disallowed from making business decisions, or if not, that they perform an impact study on their inability to use computers and pull funds from their coffers accordingly to allow for data cleansing.

That usually inspires even the thickest headed, egomaniacal MD to defer.

-k

 

[steve053]

Consider creating another table and doing a batch conversion into it on occasion with a ID from the current table. That way in the future you can just convert that which hasn't already been converted.

That's a great idea, except some of the Doc's like to add additional data. For example "4/1987-3/1991-6/1993-10/1998" could one day have 6/2006 appended to the end of the field.....grumble, grumble, grumble....

I am able to convert 90-95% of the dates correctly. Fortunately I'm pulling a limited number of records and the report runs quickly. I'm a much happier camper.

Thanks again for the base formula, it was very helpful.