Formula for Date Conversion 2

[aeckaren]

Hi all -

My name is Karen and I'm new to the forum. I am evaluating Crystal Reports (14.0.2.364) and like what I have seen so far, but I do have a question that I hope you can help me with. I have a date field that appears as follows:

991231 = December 31, 1999
1001231 = December 31, 2000

I want the date to be converted to something like mm/dd/yyyy, but I haven't been able to find a formula to convert the date correctly. This is what I have so far:

NumberVar input := {OEP70.DTTR70};
If input < 1000101 then Date (2000, 01, 01) else
Date ( Val (ToText (input, 00, "") [2 to 3]),
Val (ToText (input, 0 , "") [4 to 5]),
Val (ToText (input, 0 , "") [6 to 7]) )

This returns an error that the year must be between year 1 and year 9999. Any ideas on how to fix this formula or if I should use something else entirely?

Any help would be greatly appreciated. I have been struggling with this for a few days now!

 

[MCuthill]

Hey Karen,

You are so very close -- just missed the Math for how the year is stored (Year 100 = 2000, but since using only the 2nd and 3rd characters, the factor is 2,000 years).

Adjust your formula as follows:
{@ConvertDate}

[blue]NumberVar[/blue] input := {OEP70.DTTR70}; 
[blue]If[/blue] input < 1000101 [blue]then Date[/blue] (2000, 01, 01) [blue]else[/blue]
[blue]Date[/blue]([blue]Val[/blue]([blue]ToText[/blue](input, 00, "") [2 to 3])[red]+2000[/red],
[blue]Val[/blue]([blue]ToText[/blue](input, 0 , "") [4 [blue]to[/blue] 5]),
[blue]Val[/blue]([blue]ToText[/blue](input, 0 , "") [6 [blue]to[/blue] 7]) )

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[fisheromacse]

as is usually the case, i do not have crystal in front of me, so i may have missed something, but i think the below is close if a bit long:

{@DateFromField}
numbervar ld := len({OEP70.DTTR70});
numbervar yy;
numbervar dd := RIGHT({OEP70.DTTR70},2)
numbervar mm;

IF ld > 6 and LEFT({OEP70.DTTR70},1)="1"
THEN yy := "20" & MID({OEP70.DTTR70},2,2)
else yy := "19" & LEFT({OEP70.DTTR70},2);

IF ld > 6 then mm := MID({OEP70.DTTR70},4,2)
else mm := MID({OEP70.DTTR70},3,2);

dateserial(yy,mm,dd)

 

[aeckaren]

That worked, Mike! Thank you both for the info.