ColdFusion date functions within CFQUERY producing ISO 8601 dates

[jsidway]

I have discovered that in an environment comprised of Windows Server 2012 R2 and CF11 (or CF16) that CF date functions contained within CFQUERY will produce ISO 8601 datetime values.  For example, the value of start_dt in the following query will contain the letter 'T' between the date portion and time portion of the value of start_date:

<CFQUERY name="qry_dates" datasource="mydatasource">

     SELECT #now()# as start_dt,

     last_name

     FROM mytable

     WHERE last_name = 'Smith'

</CFQUERY>

The value of start_dt will be something like: 2016-06-28T09:38:24

My database is installed in SQL Server 2014 Standard on a server running Windows Server 2012 R2.  This did not happen with the same code where CF11 was running in WS12 R2 but the database was in an instance of SQL Server 2012 running on WS08 R2.  I need to eliminate the ISO 8601 formatting, i.e. eliminate the 'T' in the datetime string so it becomes 2016-06-28 09:38:24.  Any help would be appreciated.

[EddieLotter]

Why are you using the ColdFusion now() function inside your query? Use a SQL Server date function in the query instead.

Cheers

Eddie

[WolfShade]

datetimeformat(​start_dt,"yyyy-mm-dd HH:nn:ss")# should do it.

HTH,

^_^

[jsidway]

I realize the code is at best bad form and I had already corrected this case.  I don't know how many more instances of similar bad code there may be in the gazillion lines of code in our application.  This wasn't a problem in any previous environment so I was hoping there is a configuration/setting change in CF or SQL Server that would prevent this.  It would be a lot easier that dealing with irate customers as they bang into similar problems.

[frank000000]

It is pretty crazy.  What an interesting discovery you've made?!

I can run a query using #now()# with CF 2016 against a SQL Server 2005 DB or 2008 DB on Windows Server 2003 and it works with no problem.  It's treated as a date and the result for start_dt field can be manipulated as a date with ColdFusion.

When I run a query from CF8 using #now()# on SQL 2014 and Server 2012 r2, I have no problem.  The result for start_dt is also treated as a date.

When I run a query from CF 2016 using #now()# on SQL 2014 and Server 2012 r2, the resulting data for start_dt cannot be immediately manipulated by ColdFusion as a date.

It seems like the combination of CF 2016, Server 2012 R2, and SQL 2014 are problematic with respect to this situation.

Replacing the CF "#now()#" with the SQL Server "getDate()" function seems to do the trick.

Another option is using, "cast(#now()# as datetime) as start_dt" allows the selection data to be treated as a date throughout the ColdFusion script as expected with SQL Server 2014.

[jsidway]

I have discovered the source of the problem.  Long story short: if I roll back my CF 11 Update to Level 6 it goes away.  This is a bug in my mind but I'm not sure Adobe would agree.

[jsidway]

Correction: I only need to roll back to Update Level 7 to eliminate the problem.

[Steve Sommers]

I'm not trying to be harsh but I would consider this to be a bug on your side as opposed to a bug in CF. This is sloppy coding most likely due to a primitive knowledge of CF when it was written -- and trust me, I have come across similar code that I wrote many years back that is somehow still functioning. You should be using a cfqueryparam call to pass in the date:

     <cfqueryparam value="#now()#" cfsqltype="CF_SQL_TIMESTAMP" />

IMHO, you cannot expect Adobe or anyone to maintain backward compatibility for supporting code that should not have worked to begin with.