SAP Knowledge Base Article - Public

2324750 - Time(0) displays as Time data type on one computer, but as String data type on another, when reporting off MS SQL Server in Crystal Reports

Symptom

  • Time(0) displays as string.
  • When modifying the SQL Query of a report, the data type of some field changes from Time to String.
  • When creating a report off MS SQL Server, and entering a SQL Query that cast data as time with Time(0), it displays as a string in Crystal Reports, instead of Time.

Environment

  • SAP Crystal Reports 2008
  • 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 Crystal Reports, create a report off MS SQL Server using an ODBC connection.
  2. Add a Command Object to the report, and enter a SQL Query that convert data to time using Time(0), like:
         
        SELECT CAST('2015-03-19 01:05:06.289' AS TIME(0))
          
  3. Notice the database fields data type displays as Time in Crystal Reports.
  4. Open the report on another computer in Crystal Reports, and after editing the SQL Query, the database field of data type: Time, is now showing as: String. 

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: Time, but with the newer version of the ODBC driver, it is returning the data type: String.
          
    Below is an ODBC Trace performed showing the information sent from the ODBC Driver to Crystal Reports:
        
    - With MS SQL Server Native Client for SQL Server 2008 - SP3 ODBC Driver:

      crw32            2ca4-2dc0 EXIT  SQLBindCol  with return code 0 (SQL_SUCCESS)
      HSTMT          0x042B84E8
      UWORD         1
      SWORD         93 <SQL_C_TYPE_TIMESTAMP>
      PTR               0x04489A38
      SQLLEN         16
      SQLLEN *       0x044899C8 (0)
     

    - With a newer version of the MS SQL Server Native Client for SQL Server ODBC Driver:

      crw32           1284-a44 EXIT  SQLBindCol  with return code 0 (SQL_SUCCESS)
      HSTMT          0x081BBB18
      UWORD         3
      SWORD         -8 <SQL_C_WCHAR>
      PTR               0x13D1BB80
      SQLLEN         258
      SQLLEN *      0x05094968 (0)
      
        
    As you can see from the above trace, in the older version of the MS SQL Server Native Client ODBC Driver, it returns the data type info as: SQL_C_TYPE_TIMESTAMP, therefore the data type Time is used in Crystal Reports. But in the newer version of the MS SQL Server Native Client ODBC Driver, it provide the info: SQL_C_WCHAR, which indicate it is a string data type.

Resolution

  • To return the Time data type when using: Time(0), in an SQL Query, use the 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

CR, Time(0) , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem

Product

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