2608622 - MS SQL Server Date fields shows as text in Crystal Reports

SAP Knowledge Base Article - Public

2608622 - MS SQL Server Date fields shows as text in Crystal Reports

Symptom

  • MS SQL Server date shows as text field.
  • SQL Server date field displays as string data type in Crystal Reports.
  • On one computer, the date fields shows as date field, but on another computer the date fields shows as string field.  

Environment

  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
                
  • MS SQL Server 2008
  • MS SQL Server 2012
  • MS SQL Server 2014

Reproducing the Issue

  1. In Microsoft ODBC Administrator, create an ODBC DSN to a Microsoft SQL Server database.
  2. In Crystal Reports, create a report off MS SQL Server using an ODBC connection.
  3. Add a date field to the report.
  4. Notice the database fields data type displays as date in Crystal Reports.
  5. Open the report on another computer in Crystal Reports, and after performing a verify data source, it shows the data type as a string.
      
    Why is this occuring, and how to resolve this issue?

Cause

  • The reason the data type changes from Time, to String, it's because of the version used of the MS SQL Server Native Client ODBC Driver.
       
  • When connecting via an ODBC connection, Crystal Reports query the ODBC driver to obtain the data type of the database fields, and when using an older version of MS SQL Server Native Client ODBC Driver, it was returning the correct data type: String, but with the newer version of the ODBC driver, it is returning the data type as: Date.  The issue is with the version of the MS SQL Server ODBC Driver, and not with Crystal Reports.

Resolution

  •  For MS SQL Server Date Field to be seen as a Date data type, use the latest Microsoft ODBC Driver 11 for SQL Server.
         
  • To download the Microsoft ODBC Driver 11 for SQL Server, search for the ODBC driver on the Microsoft website. For your convenience, we added a direct link below:
          
       Microsoft ODBC Driver 11 for SQL Server  
               
             
  • Note: Microsoft ODBC Driver 11 for SQL Server, is the successor to the SQL Server Native Client ODBC driver. For more information, see Microsoft SQL Server Native Client.

Keywords

KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem

Product

SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016