SAP Knowledge Base Article - Public

2671475 - How to display milliseconds in Crystal Reports?

Symptom

  • Time missing milliseconds.
  • When inserting a date time fields that contains milliseconds on a report, it only display up to the seconds, and does not display the milliseconds.
  • How to display milliseconds in Crystal Reports?

Environment

  • SAP Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016

Reproducing the Issue

  1. In Crystal Reports, create a report off any data source.
  2. Insert on a report a Date Time or Time fields that contains milliseconds.
  3. When refresh the report, notice it only display up the seconds, like: 12:34:56, and does not show the expected milliseconds, like: 12:34:56.123

Cause

  •  Crystal Reports can display date and time, up to the seconds, but does not have the functionality to display milliseconds. 

Resolution

  • To workaround this limitation, convert the Date Time field to a String field on the database side, by using an SQL Expression Field in Crystal Reports.
                     
  • To create an SQL Expression Field in Crystal Reports:
       
    1. In Crystal Reports, open your report.
         
    2. In the "Field Explorer" pane, generally on the right side, right click on "SQL Expression Fields", and select "New..."
        
    3. Enter the name for the SQL Expression Field, like: Display milliseconds.
           
    4. In the SQL Expressiopn Field Editor, enter the SQL to convert the time stamp to string, like:

      - For MS SQL Server:

            CONVERT(VARCHAR,<INSERT YOUR DATABASE FIELD HERE>,121)
        
      - For DB2:
         
           TO_CHAR({INSERT YOIUR TIME STAMP FIELD HERE},'YYYY-MM-DD-HH24.MI.SS.FF6')  


      Important Note:   
      The above are examples. The SQL required to convert a date time to a string will be different depending the database you are using, therefore consult your database documentation, or database administrator for the SQL to use to convert a date time field to a string that output the milliseconds.
            
         
    5. Save and close the editor.
        
    6. Insert the SQL Expression Field on the report to display the time with milliseconds.

Keywords

CR, milisecond , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016