Introduction
This tip will show you how to change date format in Crystal reports when you retrieve your records from database. I'm using SQL Server 2014 as the database engine.
Using the Code
Create a table in SQL Server having the following fields...
| Field Name | Data type | Column Specification |
Lot_No | int | Primary |
Job_Status | Varchar | Not Null |
Issue_Date | DateTime | Not Null |
Customer_Name | Varchar | Not Null |
Job_Description | Varchar | Not Null |
If the field is passed in Crystal report as a DateTime, you will get a dialog that allows you to select all manner of different date/time formats, the date/time format by default looks like this 07/14/2015 12:00:00 AM/PM which is not a desired format. In this example, I'm going to change this (i.e., 07/14/2015 12:00:00 AM/PM) date format to i.e., 21-May-15.
- Right click on datetime field (i.e you are using in your Crystal report) and then select Find in Formulas.
- A formula workshop window will open. Now expand Formatting Formulas and also expand your report section which contains your date field. In my case, it is Details.
- In Details Section (i.e., search your date field), then right click on it and select New Formatting Formula.
- In this window, select Display String and press Use Editor Button.
- Type
ToText(CDate({TableName.FieldName}), "dd-MMM-yy") in formula Editor window.
- Now Press Save and Close Button on top left of Format Formula Editor Window.
- Now Press Main Report Preview button.
- You can see the changed date format in Main Report Preview. You can also change date format string as "
dd-MM-yy", "dd-MMM-yyyy", etc.
Thanks!
