CODEWITHSUNDEEP
coldfusiondate-formatcoldfusion-11
Preston
Preston

Reputation: 23

ColdFusion 11 DateFormat

I am moving one of our applications from ColdFusion 9.01 to ColdFusion 11 and encountered a situation where I cannot get the date formatted the way I want it using "DateFormat". I read through the docs since things have changed in CF versions, but I honestly can't figure out why this isn't working. It worked beautifully in CF 9. I know it's probably something very easy, but I am just not seeing it.

The query (Oracle DB) provides me a list of the last 30 days and the loop is simply to reformat the date output from "2014-07-01 00:00:00.0" to a more friendly looking display of 01-Jul-2014 except that I cannot get it to format as "dd-mmm-yyyy" it just spits back the original output from the query. I hard coded the date where normally there would be a cfquerparam. Any ideas?

<cfquery name="qryDateArray" datasource="#request.db#">
  select trunc(to_date('07/01/2014', 'mm/dd/yyyy') + 1 - rownum) as ref_date
  from dual connect by rownum <= 30
</cfquery>

<cfloop from="1" to="#qryDateArray.recordcount#" index="j">
  <cfset qryDateArray.ref_date[j] = DateFormat(qryDateArray.ref_date[j], "dd-mmm-yyyy")>
</cfloop>

<cfoutput>
  <cfdump var="#qryDateArray#">
</cfoutput>

Upvotes: 2

Views: 1065

Answers (2)

Snipe656
Snipe656

Reputation: 845

I could not test this on CF11 since I do not have it handy. I did verify that your code though returns results as you explained when I ran it on my CF10 environment here. So what you can do is add a column to the query object and define it as a varchar and add your formatted data to that. This in turn dumped out the formatted dates.

<cfquery name="qryDateArray" datasource="#request.db#">
  select trunc(to_date('07/01/2014', 'mm/dd/yyyy') + 1 - rownum) as ref_date
  from dual connect by rownum <= 30
</cfquery>

<cfset aryData  = [] />

<cfloop from="1" to="#qryDateArray.recordcount#" index="j">
  <cfset ArrayAppend(aryData, DateFormat(qryDateArray.ref_date[j], "dd-mmm-yyyy")) />
</cfloop>

<cfset QueryAddColumn(qryDateArray, "STRDATE", "VarChar", aryData) />

<cfoutput>
  <cfdump var="#qryDateArray#">
</cfoutput>

enter image description here

If dependent on the query column names then could use something like Ben's method explained here to do some renaming of the columns: http://www.bennadel.com/blog/357-ask-ben-changing-coldfusion-query-column-names.htm

Upvotes: 1

Adam Cameron
Adam Cameron

Reputation: 29870

It'd be great if you'd given us a portable test case rather than one that relies on your database, but I suspect it is because ColdFusion has become more rigid with its type management of query columns.

So CF considers your ref_date column to be of type date, so when you try to put the formatted string back into the query column, CF tries (and succeeds) to convert the string back into a date.

Aside:

I have to wonder why you don't format the data string in the DB from the outset, and just return it the way you need it, rather than returning something else, then looping over the thing to adjust it..?

Upvotes: 1

Related Questions