Highlighted

ColdFusion 2018 MySQL 8 datetime field error

New Here ,
Dec 17, 2018

Copy link to clipboard

Copied

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

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,

^ _ ^

Views

796

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

ColdFusion 2018 MySQL 8 datetime field error

New Here ,
Dec 17, 2018

Copy link to clipboard

Copied

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

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,

^ _ ^

Views

797

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Dec 17, 2018 0
Adobe Community Professional ,
Dec 17, 2018

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 17, 2018 0
LEGEND ,
Dec 17, 2018

Copy link to clipboard

Copied

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 17, 2018 1
New Here ,
Dec 27, 2018

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 27, 2018 0
LEGEND ,
Dec 27, 2018

Copy link to clipboard

Copied

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 27, 2018 0
LEGEND ,
Jan 09, 2019

Copy link to clipboard

Copied

Thank you for marking my answer as correct.  I do appreciate it.

V/r,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 09, 2019 0