Convert Varchar to Date 1

[Hepburn]

Happy 4th!

This is my situation. I have two fields in the database that are varchar (I would make them date fields but another person in the office won't). My data looks like this:

- 09/01/07
- 06-01-2007
- March 2008
- December 31. 2007
- 06012007

The format I want to arrive at is 09/07 (MM/YY).

I have the following, but it doesn't work in the last two records (December 31. 2007 or 06012007):


if isdate({CERTIFICATIONDATE}) then
cdate({CERTIFICATIONDATE});

Thanks in advance...

 

[lbass]

The following works for your examples:

stringvar x := {table.stringdate};
stringvar mo;
stringvar yr;

if isnumeric(left(x,1)) then
mo := left(x,2) else
mo := (
select left(x,3)
case 'Jan' : '01'
case 'Feb' : '02'
case 'Mar' : '03'
case 'Apr' : '04'
case 'May' : '05'
case 'Jun' : '06'
case 'Jul' : '07'
case 'Aug' : '08'
case 'Sep' : '09'
case 'Oct' : '10'
case 'Nov' : '11'
case 'Dec' : '12'
);
yr := right(x,2);
mo+"/"+yr

-LB

 

[Hepburn]

Thanks lbass You made my day.

It works perfectly.