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 YYYYMMDDHHMMSS. If you don't have time use the second example
below:
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)
