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).

Merger

From CF11+ you can use a custom pluggable serializer that formats dates in a different way (see docs, scroll all the way down). However, dates must be parsed and converted by the receiver anyway in JSON, because JSON does not specify a date format. Therefore, you are bound to call new Date() on those strings one way or the other - and CFs format seems to work well with that. – Weasand

Dude it is JavaScript Object Notation. It's a mechanism for serialising JS objects. That other systems also have decided subsequently it's a convenient format is neither here nor there, so it stands to reason the serialised values will be JS-friendly. – Raphaelraphaela

@AdamCameron per json.org it's a completely language independent data-interchange format. I don't think it was ever intended to only be used in context of JavaScript. – Merger

@Weasand in this case it's PHP consuming the output. I haven't seen the PHP code, so I don't know if new Date() ends up being involved (or how PHP handles date strings in various formats, other than that it chokes when it sees a comma after the month). As you say, dates aren't considered in the JSON spec, so I guess replacing one string with another is a CF serialization implementation assumption/convenience rather than anything to do with the rendered JSON or how it's handled by the consumer. – Merger

@Weasand btw, thanks for the heads up about the custom serializer. I will have to give that a go. – Merger

PHP needs to parse dates out of the JSON as well, how it digests CFs interesting format I cannot say. Try that first before you change anything. – Weasand

how PHP handles date strings Same as most languages I would imagine. There is almost certainly a date function that allows you to supply a "mask" for parsing the input. – Glamorous

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.

Brazzaville

This is what we ended up doing, and it worked well. If CF doesn't recognize it as a "date" it doesn't know to be "helpful" :) Here's hoping a future version of CF doesn't improve the ability to identify dates before adding a flag for suppressing the helpfulness (or providing a format string argument!) – Merger

For MySQL I used SELECT CAST(START_DATE as char) as START_DATE as a work around for this issue. – Flexure

Answer 2

If you are using QuerySetCell to build your query (or if your date is not in a query) then ToString() will force Coldfusion to leave your date as it is.

Argot