Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Add sendTimeAsDatetime option to MSSQL Driver in Admin

Description

To support the CF_SQL_TIME format in the ODBC driver, you need to add the parameter "sendTimeAsDatetime=true" to the connection string.

See the following ticket for more info:
https://github.com/microsoft/mssql-jdbc/issues/559

This works when setting a datasource in application.cfc, but there is no way to add this through the admin.

Without this setting, the following error is generated when trying to query a time field: "The data types time and datetime are incompatible in the equal to operator."

Environment

Lucee 5.3.8.189 and MS-SQL Server Driver 8.4.1

Activity

Show:

Pothys - MitrahSoft 23 July 2021 at 14:04

In lucee 6, create or update datasource using Administrator.cfc supports the argument custom as a struct. So we can pass any parameters for the connection string in custom struct https://luceeserver.atlassian.net/browse/LDEV-3469

Sean P. Ford 23 July 2021 at 13:40

Thank you. I copied the datasource config from the bottom of the admin page, added it to my application.cfc, then added the "sendTimeAsDatetime=true" to the connection string that way. Just thought this should eventually find its way into the admin at some point.

If you follow the MS ticket through a couple of years and subsequent tickets, it seems that at some point this will become the default, but they’ve been discussing it for years, so no clue as to when that will be.

Pothys - MitrahSoft 23 July 2021 at 13:34

I've checked this ticket and yes, the admin doesn't have an option to use the sendTimeAsDatetime for MSSQL datasource.

For a workaround, create a datasource using type other-JDBC(deprecated). You can use your own connection string like Application.cfc. But please keep in mind this option is marked as deprecated. So there is a possibility to remove this option in the future.

Details

Assignee

Reporter

Priority

Labels

New Issue warning screen

Before you create a new Issue, please post to the mailing list first https://dev.lucee.org

Once the issue has been verified, one of the Lucee team will ask you to file an issue

Affects versions

Created 22 July 2021 at 20:41
Updated 23 July 2021 at 15:59

Flag notifications