phone: (540)338-0194
email: ken@kenhamady.com

´╗┐

 Crystal Reports formula examples

Character Date to True DateTime or Date:

These formulas convert Dates stored as character values into true date values. The first two are for strings that are formatted as "YYYYMMDDHHMMSS" or "YYYYMMDD", while the third example works for strings that are formatted as "MM/DD/YYYY" or "M/D/YY".  One warning when using these formulas.  If you have invalid values or blanks it will cause the formula and the report to generate an error and stop.  You can use an IF-THEN statement to check for blanks or values out of range. 

1) This formula assumes that you have a fixed length string with the format YYYYMMDDHHMMSS.   If you don't have time use the second example below: 

StringVar DT :=  {table.TextDate}; // place your field here
DateTime (
Val (DT [  1 to  4 ]),
Val (DT [  5 to  6 ]),
Val (DT [  7 to  8 ]),
Val (DT [  9 to 10 ]),
Val (DT [ 11 to 12 ]),
Val (DT [ 13 to 14 ]) )

2) This formula assumes that you have a fixed length string with the format YYYYMMDD (or you have YYYYMMDDHHMMSS and don't need the time):   

StringVar DT :=  {table.TextDate}; // place your field here
Date (
Val (DT [  1 to  4 ]),
Val (DT [  5 to  6 ]),
Val (DT [  7 to  8 ]) )

3) These formulas can adjust for dateas formatted as "MM/DD/YYYY" or "M/D/YY".  In other words, it assumes that your character string looks like a typical date with slash separators such as 12/31/1997.  If you have a 4-digit year the formula places it in the correct century.  If you have a 2-digit year it will assume that you are in 19xx if the year is greater than 50 and 20xx if the year is less than 50.   This cutoff year can be changed from 50 to another number. 

The first formula is for versions 8.5 and later while the second version is for older versions of Crystal. 

(Version 8.5 and later)
StringVar DT := table.TextDate}; // place your field here
NumberVar YR := Val (Split (DT , '/')[3]);

if YR < 50   then YR := YR + 2000 else
if YR < 1000 then YR := YR + 1900;

Date ( YR,
Val (Split (DT , '/') [1]),
Val (Split (DT , '/') [2]) )

(Older versions that do not have the SPLIT() function)

WhileReadingRecords;
StringVar TextString := {table.TextDate}; // place your field here
NumberVar Slash1 := instr(TextString,"/");
NumberVar Slash2 := instr(4,TextString,"/");

NumberVar Yr := If Length (TextString) - Slash2 > 3

then Val (right(TextString,4))
else
if Val (right(TextString,2)) < 50    

then Val (right(TextString,2))+2000
else Val (right(TextString,2))+1900;

NumberVar Mth := Val (Left (TextString,Slash1-1));
NumberVar Dy := Val (TextString [Slash1+1 to Slash2-1]);
Date(Yr, Mth, Dy)