Now() sets a value to the millisecond and I think this is a change from 4.5.x to 5.1.x.
Someplace in my code I set a session variable that is accessed later in a query.
session.savedAt = Now();
followed by:
qryObjects = queryExecute(
sql = "SELECT objectid FROM tblObjects WHERE accountid=:accountid AND ISNULL(deletedat) AND savedat=:savedat",
params = {accountid:{sqltype:"integer", value:10001}, savedat:{sqltype:"timestamp", value:createODBCDateTime(session.savedat)}},
options = {datasource:application.dsn}
);
This returns no records. If I dump qryObjects, it shows me that the SQL is:
SELECT objectid FROM tblObjects WHERE accountid=10001 AND ISNULL(deletedat) AND savedat={ts '2017-06-14 17:18:35'}
If I run that code directly on MySQL, I get the expected result. So, why does a query on MySQL return a different result than the same query run via Lucee?
Well, I turned on logging on my MySQL server and found that the query that Lucee was actually sending was:
SELECT objectid FROM tblObjects WHERE accountid=10001 AND ISNULL(deletedat) AND savedat={ts '2017-06-14 17:18:35.385'}
Since my table was storing datetime, it never saved a millisecond value. But now any query that uses a time based on Now() sends a millisecond value, though you can only tell if you look at your MySQL logs since Lucee reports the timestamp value only to the second.