Coldfusion's SerializeJSON does not get along with MS SQL 2008

0
votes

I'm on Adobe Coldfusion 9.0.1 and Microsoft SQL Server 2008 R2 and confirm the following on 2008 Express. I have two tables which I JOIN and serialize the result to a JavaScript Variable using Coldfusion's own SerializeJson function.

CREATE TABLE [dbo].[event](
[id] [int] IDENTITY(1,1) NOT NULL,
[date1] [date] NULL,
[date2] [datetime] NULL,
[date3] [datetime2](7) NULL,
[status] [nchar](10) NULL
);

CREATE TABLE [dbo].[status](
[id] [int] IDENTITY(1,1) NOT NULL,
[text] [nvarchar](max) NULL,
);

My first CFQuery looks like

<cfquery name="qryprod" datasource="mssqlexpress">
    SELECT  e.id, date1, date2, date3
    FROM    event e
    inner join  status s
    on  e.status = s.id
    WHERE   e.id = <cfqueryparam cfsqltype="cf_sql_numeric" value="2">
</cfquery>
#SerializeJson( qryprod, true )#

All three dates are serialized to something like: Februar, 27 2012 22:10:12 - Monthnames in German.

My second Query:

<cfquery name="qryprod" datasource="mssqlexpress">
    WITH subStatus AS (
        SELECt  id, text
        FROM    status
    )
    SELECT  e.id, date1, date2, date3
    FROM    event e
    inner join  subStatus s
    on      e.status = s.id
    WHERE   e.id = <cfqueryparam cfsqltype="cf_sql_numeric" value="2">
</cfquery>
#SerializeJson( qryprod, true )#
  • date1 of type date is serialized to 2012-02-27
  • date2 of type datetimeis serialized to Februar, 27 2012 22:10:12
  • date3 of type datetime2 looks like 2012-02-27 22:10:12.4400000

How does that happen and what do I have to do to always get my dates in the form Monthname, day year?

Best, Bernhard

2
jsonsql-server-2008coldfusioncoldfusion-9
You might be able to figure out why this is happening by looking at the resultSetMetaData, and pulling up the column types returned to coldfusion. - Mark
Thanks for the reply. Someone in real life advised to check the JDBC driver. Microsoft provides information they fixed something with date, datetime2 and DATETIMEOFFSET in a current update: microsoft.com/download/en/details.aspx?id=28562 I used this driver instead of the one provided with Coldfusion and it worked as I originally expected. - Bernhard Döbler
The JDBC driver provided with the beta of Coldfsuin 10 delivers the expected results. Just tried it. - Bernhard Döbler

2 Answers

0
votes

You will probably need to loop through the query result, and change the date values to strings manually. You can use ParseDateTime and DateFormat (or their localized versions, LSParseDateTime and LSDateFormat) to accomplish this.

0
votes

Take a look at the T-SQL CONVERT function.

SELECT  e.id, CONVERT(varchar(15), date1, 107) AS date1...

The above format code will return mon dd, yyyy format.

If you want the full month name, you can use date functions:

SELECT  e.id, 
(
DATENAME(month, date1) + ' ' + 
CAST(DATEPART(d, date1) AS varchar(2)) + ', ' + 
CAST(DATEPART(yyyy, date1) AS varchar(4))
) AS date1
...