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

• 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 of 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.

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

1. In Crystal Reports, create a report of 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. 

• The reason the data type changes from Time, to String, it's because of the version used of the MS SQL Server 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 one version of MS SQL Server ODBC Driver, it was returning the correct data type: Time, but with with another 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 one MS SQL Server 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 another version of the MS 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 one version of the MS SQL Server 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 other version of the MS SQL Server ODBC Driver, it provide the info: SQL_C_WCHAR, which indicate it is a string data type.

• To return the Time data type when using: Time(0) in an SQL Query, use the latest update of the supported Microsoft SQL Server ODBC Driver.
                 
  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):
         
         2859510 - Supported Platforms documents for Crystal Reports  
            
  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, the Microsoft web site link is: 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

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