ColdFusion serializeJSON date format (bug?)

Question

I noticed something funny with serializeJSON when it's passed a query containing dates (in this case, from SQL Server, but it could be other date data).

When I inspect the query before it's passed in the date looks like this:

2000-09-05 00:00:00.0

The generated JSON looks like this:

{"COLUMNS":["START_DATE"],"DATA":[["September, 05 2000 00:00:00"]]}

I understand from the docs that the dates are intended to be acceptable for use in a JavaScript Date object. Aside from the debatable design decision of assuming that's how everyone wants dates formatted coupled with that to not provide for a way to disable this obligatory helpfulness, I am noticing that the comma is in an odd location.

I would expect September 05, 2000 00:00:00 rather than having the comma after the month.

Is there any way to get the serializeJSON function to leave the dates alone or to specify a format string? If not I suppose I'll be reduced to using something like regexreplace after it's generated to repair the damage (since the php site consuming the output doesn't recognize the comma-after-month version as a valid date).

Answer 1

In your query instead of

SELECT START_DATE
FROM ...

use

SELECT convert(varchar(25), START_DATE, 120) as START_DATE
FROM ...

then serializeJSON will treat it as a string and will leave it alone.