-lw-scaled.gif.png){kind=logo}
While working with a Ripplestone client today that had a Crystal Reports report that had data that was a date but the database stored the date as a number in the format YYYYMMDD (20090924 for example). They wanted the date to be displayed in Ripplestone as MM/DD/YYYY (09/24/2009).
In their copy of Crystal Reports Designer they had changed the default number format to only display the number with no formatting. They then used the following formula to convert the number into a date format:
This worked great when the number was 20090924, the formula split the number into the following 3 numbers 09, 24 and 2009 and then created the date by adding the “/” between the numbers.
The problem started once the report was published to Ripplestone and run. The dates on the report were displaying as 90/00/20,0
After a lot of looking at the formula we discovered that the default number for the Crystal Reports Runtime Engine is to add commas and 2 decimals to the number, so that it was displaying as 20,090,924.00
When this was run with the formula, the 3 number were 90, 00 and 20,0.
To fix this problem we needed to remove the formatting from the number before the Mid, Right and Left functions were applied. To remove the formatting of the number I added extra attributes to the ToText function. The first attribute was the “#” as the second value. This removes the commas from the number. The next attribute was a zero (0) as the third value. This will set the decimals to be zero and remove the “.00” from the end of the number. The revised formula is below, with the changes highlighted in red.
This allowed the number to be formatted correct and when the report was run or scheduled from Ripplestone the dates are displayed in the correct format.
In their copy of Crystal Reports Designer they had changed the default number format to only display the number with no formatting. They then used the following formula to convert the number into a date format:
Mid (ToText ({Database Field}), 5,2) + “/” + Right ( ToText({Database Field}),2) + “/” + Left (ToText ({Database Field}), 4)
This worked great when the number was 20090924, the formula split the number into the following 3 numbers 09, 24 and 2009 and then created the date by adding the “/” between the numbers.
The problem started once the report was published to Ripplestone and run. The dates on the report were displaying as 90/00/20,0
After a lot of looking at the formula we discovered that the default number for the Crystal Reports Runtime Engine is to add commas and 2 decimals to the number, so that it was displaying as 20,090,924.00
When this was run with the formula, the 3 number were 90, 00 and 20,0.
To fix this problem we needed to remove the formatting from the number before the Mid, Right and Left functions were applied. To remove the formatting of the number I added extra attributes to the ToText function. The first attribute was the “#” as the second value. This removes the commas from the number. The next attribute was a zero (0) as the third value. This will set the decimals to be zero and remove the “.00” from the end of the number. The revised formula is below, with the changes highlighted in red.
Mid (ToText ({Database Field}, “#”, 0), 5,2) + “/” + Right ( ToText({Database Field}, “#”, 0),2) + “/” + Left (ToText ({Database Field}, “#”, 0), 4)
This allowed the number to be formatted correct and when the report was run or scheduled from Ripplestone the dates are displayed in the correct format.