SAP Knowledge Base Article - Public

2608622 - MS SQL Server Date fields or Stored Procedure Date Parameters, 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.
  • MS SQL Server Stored Procedure Date Parameter, prompt as a String Parameter in Crystal Reports.
  • On one computer, the date fields shows as date, but on another computer the date fields shows as string.
  • When refreshing a report on one computer, it refresh successfully, but on another one, it generates error in formula because the data type changed from a Date to a String.

Environment

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

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 of 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 a Date, to a String, it's because the ODBC Driver used to connect was the MS SQL Server (SQLSRV32.DLL) or another older MS SQL Server ODBC driver, which is not supported to connect to the latest versions of MS SQL Server.
         
  • When connecting via an ODBC connection, Crystal Reports query the ODBC driver to obtain the data type of the database fields, and some unsupported MS SQL Server ODBC Drivers are returning the incorrect data type: String, instead of a Date, but with a supported version of the ODBC driver, it is returning the data type: 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, or Stored Procedure Date Parameter, to be seen as a Date data type, use a supported ODBC driver for the MS SQL Server version you are connecting to:
            
    1. Confirm the supported version of Microsoft SQL Server ODBC driver, for the version of MS SQL Server you want to report from, by looking at the Crystal Reports supported platforms (PAM):
    2. From the Microsoft website, download the supported MS SQL Server ODBC driver for the version of MS SQL Server you are using.  For convenience, we added the Microsoft website link below:
               
           Microsoft SQL Server ODBC driver download
                 
    3. Install the MS SQL Server ODBC driver.
            
    4. In the 32bit Microsoft ODBC Administrator, create an ODBC DSN using the supported ODBC driver.
            
      Important Note before creating the ODBC DSN:
      • If no other applications are using the ODBC DSN the report use, you can rename or delete the ODBC DSN that uses the incorrect MS SQL Server ODBC driver.
      • If there is other application using the ODBC DSN the report use, then create a new ODBC DSN to MS SQL Server, and in Crystal Reports, perform a set data source location for the report, to point the new ODBC DSN

Keywords

CR, CRDB , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

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