Before SQL server 2008 all date values were stored as DateTime values, even if you didn’t need the time portion. Starting with SQL Server 2008 you could a column either as a Date (with no time) or a DateTime. But I have noticed, recently, that anytime I create a field with a “Date” type, Crystal sees the field as a string instead of a date. So even though I usually don’t need time values, I typically create my table fields and calculations as DateTimes. That way Crystal can format the fields with date options and do date calculations in the report.

But one of my customers recently asked me about this. She found that this only happens if you use the SQL Server ODBC driver. Apparently, the SQL Server Native Client doesn’t convert date fields to strings. So I did a test by creating two DSN’s to a test database and a test table. One DSN uses the SQL Server ODBC driver (10.00.17134.01) from 2018. The other uses the SQL Server Native Client 11 (2011.1102100.60) from 2011. Sure enough, a report using the Native Client maintained the date value as a date, while the ODBC driver converted it to a string.

Then I read this page where Microsoft now recommends using OLEDB:

When I first tried OLEDB I saw two providers. They gave me the same two results as above, which told me that these providers were using the same two drivers I had just tested with ODBC. That is when I realized that the article was talking about a newer OLEDB driver. I downloaded and installed this driver but if you aren’t careful it is easy to miss it in the list of providers. It looks very much like the old one. The only difference between the new one and the old one is that the new one uses the word “driver” while the old one uses the word “provider”.

Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL)
Microsoft OLE DB Provider for SQL Server (SQLOLEDB)

The name in parens is what you see under connection “properties” in Crystal’s “Set Datasource Location” window. When I used the new MSOLEDBSQL driver I got date values.

And, thanks to Laurie Weaver, a developer at Wyse Solutions, for letting me know this behavior was driver related.