datetime2 data type (SQL 2008) in Crystal 2008

[Former Member]

Hi, I have a report containing a datetime2 field, however Crystal is interpreting it as a string.

I have tried converting with DateTimeValue, and extracting just the time via Mid() and converting that with TimeValue(), however it's not recognising the datetime2 string as a valid datetime in Crystal 2008.

Does anyone have a trick for making a datetime2 field be recognised as a DateTime in Crystal 2008, so I can apply DateTime formatting?

Thanks,

+M

[former_member11696 · Accepted solution]

Hi Mark,

CR will convert any field type is does not know what to do with into a string field. The reason it doesn't know what ype may be due to the driver and this is a new field type for MS SQL 2008. I am assuming you are using OLE DB, try creating a System ODBC DSN and see if that works. There are known issues with MS's OLE DB driver.

The other requirement is that MS does not support the MDAC version of their DB clients to connect to 2008. You must install the MS SQL Server Client on the local PC. You'll see a new driver installed, try using that driver also.

Thank you

Don

[Former Member · Accepted solution]

Thanks, that fixed it.

The report was originally created on SQL 2005. When the fields were updated to DateTime2, the SQL Client version had not been updated. In the OLE DB login, I just hit "Back" and changed the driver from "SQL Native Client" to "SQL Native Client 10.0". That made the field type be correctly recognised as DateTime.

Thanks for you assistance, and Merry Christmas!

+M

[Former Member]

Thanks for the suggestion: I'd tried this, as well as CDateTime (I need both), but get "Bad date format string" when the function is evalutaed.

The problem is Crystal is converting a valid DateTime2 field into a String, which it then can't convert back into a date, time or datetime.

+M

[Former Member]

try using CDATE()