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,
^ _ ^
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
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,
^ _ ^
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
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,
^ _ ^
Copy link to clipboard
Copied
Thank you for marking my answer as correct. I do appreciate it.
V/r,
^ _ ^