Conversion to a Date Format

[TEM3]

Using Crystal Reports 8.5 and Oracle 9i tables:

I have a bunch of "dates" in a text table field that Crytal Reports is incapabible of converting with the todate function. They are all in the format of, for example, 26FEB2008.

Any suggestions for a formula that would convert a intended date like "26FEB2008" to "02/26/2008"? What these record field all have in common is that they are 9 characters long and all start with two digits (in string form).

 

[Madawc]

Are you sure ToDate won't convert them? Try

ToText(ToDate({your.date}), "MM/dd/yyyy")

I no longer have 8.5, maybe that version fails. If so, use Left, MID and RIGHT to split the day, month and year and to convert. FEB = 2 etc and re-assemble using Date(yyyy, mm, dd)

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[TEM3]

This formula seems to works.....

if len(trim({ARRESULTASVARCHAR.RESULTASVARCHAR})) = 9 then

(
if Mid({ARRESULTASVARCHAR.RESULTASVARCHAR},3 ,3) = "JAN" then ("01/" & Left({ARRESULTASVARCHAR.RESULTASVARCHAR},2) & "/" & Mid ({ARRESULTASVARCHAR.RESULTASVARCHAR},6 ,4)) else
if Mid({ARRESULTASVARCHAR.RESULTASVARCHAR},3 ,3) = "FEB" then ("01/" & Left({ARRESULTASVARCHAR.RESULTASVARCHAR},2) & "/" & Mid ({ARRESULTASVARCHAR.RESULTASVARCHAR},6 ,4)) else
if Mid({ARRESULTASVARCHAR.RESULTASVARCHAR},3 ,3) = "MAR" then ("01/" & Left({ARRESULTASVARCHAR.RESULTASVARCHAR},2) & "/" & Mid ({ARRESULTASVARCHAR.RESULTASVARCHAR},6 ,4)) else
if Mid({ARRESULTASVARCHAR.RESULTASVARCHAR},3 ,3) = "APR" then ("01/" & Left({ARRESULTASVARCHAR.RESULTASVARCHAR},2) & "/" & Mid ({ARRESULTASVARCHAR.RESULTASVARCHAR},6 ,4)) else
if Mid({ARRESULTASVARCHAR.RESULTASVARCHAR},3 ,3) = "MAY" then ("01/" & Left({ARRESULTASVARCHAR.RESULTASVARCHAR},2) & "/" & Mid ({ARRESULTASVARCHAR.RESULTASVARCHAR},6 ,4)) else
if Mid({ARRESULTASVARCHAR.RESULTASVARCHAR},3 ,3) = "JUN" then ("01/" & Left({ARRESULTASVARCHAR.RESULTASVARCHAR},2) & "/" & Mid ({ARRESULTASVARCHAR.RESULTASVARCHAR},6 ,4)) else
if Mid({ARRESULTASVARCHAR.RESULTASVARCHAR},3 ,3) = "JUL" then ("01/" & Left({ARRESULTASVARCHAR.RESULTASVARCHAR},2) & "/" & Mid ({ARRESULTASVARCHAR.RESULTASVARCHAR},6 ,4)) else
if Mid({ARRESULTASVARCHAR.RESULTASVARCHAR},3 ,3) = "AUG" then ("01/" & Left({ARRESULTASVARCHAR.RESULTASVARCHAR},2) & "/" & Mid ({ARRESULTASVARCHAR.RESULTASVARCHAR},6 ,4)) else
if Mid({ARRESULTASVARCHAR.RESULTASVARCHAR},3 ,3) = "SEP" then ("01/" & Left({ARRESULTASVARCHAR.RESULTASVARCHAR},2) & "/" & Mid ({ARRESULTASVARCHAR.RESULTASVARCHAR},6 ,4)) else
if Mid({ARRESULTASVARCHAR.RESULTASVARCHAR},3 ,3) = "OCT" then ("01/" & Left({ARRESULTASVARCHAR.RESULTASVARCHAR},2) & "/" & Mid ({ARRESULTASVARCHAR.RESULTASVARCHAR},6 ,4)) else
if Mid({ARRESULTASVARCHAR.RESULTASVARCHAR},3 ,3) = "NOV" then ("01/" & Left({ARRESULTASVARCHAR.RESULTASVARCHAR},2) & "/" & Mid ({ARRESULTASVARCHAR.RESULTASVARCHAR},6 ,4)) else
if Mid({ARRESULTASVARCHAR.RESULTASVARCHAR},3 ,3) = "DEC" then ("01/" & Left({ARRESULTASVARCHAR.RESULTASVARCHAR},2) & "/" & Mid ({ARRESULTASVARCHAR.RESULTASVARCHAR},6 ,4))
);