Skip to main content
S
stuartw81
Participating Frequently
December 17, 2018
Answered

ColdFusion 2018 MySQL 8 datetime field error

  • December 17, 2018
  • 2 replies
  • 2017 views

Hi

We're in the process of setting up our new Windows 2016 server running ColdFusion 2018 with Update 1 installed. We run MySQL 8 and are using the most current Connector (mysql-connector-java-8.0.13.jar).

We're having a problem with a SELECT statement when the only field chosen is a DATETIME field that is only being used for the time section. The returned value from the database is '0000-00-00 00:01:00' (ie 1 hour).

SELECT Employee_LunchBreak_Length

FROM t_Employee

WHERE t_Employee_ID = '1'

The query above returns a 500 error and creates the following entry in the Application log.

"Error Executing Database Query.Zero date value prohibited The specific sequence of files included or processed is: (*path to template*)"

The query works in both MySQL Workbench and in PHP without issue. It also works on our previous CF10 / MySQL 5.6 server.

On both the new server (CF18) and old server (CF10) we're setting a connection string on the DSN of zeroDateTimeBehavior=CONVERT_TO_NULL&characterEncoding=utf8

I've seen problems with invalid TIMESTAMP fields, but understood that it was OK to use partial values in DATETIME fields.

Can anyone shed any light on this for me please?

Kind regards, Stuart

This topic has been closed for replies.
Correct answer WolfShade

stuartw81  wrote

the only field chosen is a DATETIME field that is only being used for the time section.

This is, most likely, the culprit.  0000-00-00 is an invalid date.  The column name "Employee_Lunchbreak_Length" should not be a DATETIME datatype.  Try TIME or TIMESTAMP datatypes.

UPDATE:  TIMESTAMP also uses year-mon-date as part of the format.  So, I think you need the TIME datatype.

HTH,

^ _ ^

2 replies

WolfShade
WolfShadeCorrect answer
Legend
December 17, 2018

stuartw81  wrote

the only field chosen is a DATETIME field that is only being used for the time section.

This is, most likely, the culprit.  0000-00-00 is an invalid date.  The column name "Employee_Lunchbreak_Length" should not be a DATETIME datatype.  Try TIME or TIMESTAMP datatypes.

UPDATE:  TIMESTAMP also uses year-mon-date as part of the format.  So, I think you need the TIME datatype.

HTH,

^ _ ^

    S
    stuartw81Author
    Participating Frequently
    December 27, 2018

    Hi WolfShade, yes changing the field's datatype has worked, thanks.

    It's odd that MySQL workbench allowed these values, but then ColdFusion would throw the 500 error. I would have expected a more descriptive standard message.

    Many thanks, Stuart

    WolfShade
    WolfShade
    Legend
    December 27, 2018

    I have no explanation for the discrepancy, unfortunately.

    Please mark my answer as correct, for any future users who have the same issue.

    V/r,

    ^ _ ^

    D
    Community Expert
    Dave WattsCommunity Expert
    Community Expert
    December 17, 2018

    I don't know the answer to this question. But what I'd recommend is that you perform some sort of casting or conversion right within the query to return a usable value.

    Dave Watts, Fig Leaf Software

    Dave Watts, Eidolon LLC
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices