1207075 - How to change the date format in the prompting window from yyyy/mm/dd format to dd/mm/yyyy (Europe) or mm/dd/yyyy (US) format

SAP Knowledge Base Article - Public

1207075 - How to change the date format in the prompting window from yyyy/mm/dd format to dd/mm/yyyy (Europe) or mm/dd/yyyy (US) format

Symptom

When you create a date parameter, the prompting window shows yyyy/mm/dd format only. How can you change the date format to dd/mm/yyyy (Europe) or mm/dd/yyyy (US) format?
 

Cause

The yyyy/mm/dd format is by design and hard coded in Crystal Reports. It can be confusing for users as the standard recognized format is dd/mm/yyyy in Europe or mm/dd/yyyy in the US.
 

Resolution

To change the date format in the prompt into the Europe format dd/mm/yyyy:

NOTES

  • To reflect the US format, simply swap the values for dd/mm into mm/dd
  • You will not be able to change this by modifying the regional settings.
 
  1. Click View menu > Field Explorer > Parameter Fields > New button. Create a parameter named "FromDate" of data type string and give it an edit mask of 00/00/000. It will prompt for the From day, From month and From year from the FromDate parameter field.
  2. Create a second parameter named "ToDate" of data type string and give it an edit mask of 00/00/000. It will prompt for the To day, To month and To year from the ToDate parameter field.
  3. Create 3 formulas for each parameter (6 in total) to associate the input to 2 complete dates (as it is a date range). Click Report > Formula Workshop > New button to create a formula. The formulas for the FromDate (start date) are as follows:
 Formula name  Formula
 FromDay  tonumber(left({?Fromdate},2))
 FromMonth  tonumber(mid({?Fromdate},4,2))
 FromYear  tonumber(mid({?Fromdate},7,4))
  1. The formulas for the ToDate (end date) are as follows:
 Formula name  Formula
 ToDay  tonumber(left({?ToDate},2))
 ToMonth  tonumber(mid({?ToDate},4,2))
 ToYear  tonumber(mid({?ToDate},7,4))
  1. Click Report > Select Formula > Record. Type something similar to the formula below (your report field will likely be different than {Orders.OrderDate}):
{Orders.OrderDate} >= Date ({@FromYear},{@FromMonth}, {@FromDay}) and
{Orders.OrderDate} <= Date ({@ToYear},{@ToMonth},{@ToDay})
 
When refreshed and prompted with new parameter values, you can then enter the dates in the required dd/mm/yyyy format.

Keywords

Changing date format prompting window yyyy/mm/dd dd/mm/yyyy , 9571493 , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To

Product

SAP Crystal Reports 8.0 ; SAP Crystal Reports XI ; SAP Crystal Reports XI R2