- 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.
- 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
- In Crystal Reports, create a report off MS SQL Server using an ODBC connection.
- 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))
- Notice the database fields data type displays as Time in Crystal Reports.
- 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 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)
SWORD 93 <SQL_C_TYPE_TIMESTAMP>
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)
SWORD -8 <SQL_C_WCHAR>
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.
- 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.
CR, Time(0) , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem
Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016