Check the column in the query at runtime and replace a value

[meensi]

Hi all,

Any help would be highly appreciated.

I m having a query , in that there are two columns which are date, I need to format this date to display it in Excel.

For that , I need to check which the column is of DATE( which I can find, we are having column names which ends with _DATE, )

After I find the column name, i need to format this column value.

Here is my code,

<cfquery name="downloadquery" datasource="testDB">
   
    SELECT ALL SHOP.SHOP_RCRD_ID, SHOP.RCVD_DATE,
    SHOP.REMV_DATE,
    FROM SHOP, BUSINESS WHERE SHOP.SHOP_RCRD_ID=BUSINESS.SHOP_RCRD_ID AND ROWNUM<=2000
    AND SHOP.RCVD_DATE<= TO_DATE('03/30/2011','MM/DD/YYYY')
    AND rownum < 10 ORDER BY RCVD_DATE

</cfquery>

<cfset arrTest = ListToArray(downloadquery.ColumnList)/>

<cfloop from="1" to="#ArrayLen(arrTest)#" index="i">
       <cfif Find("_DATE", #arrTest#,1) NEQ 0>

           <cfloop index="j" from="1" to="#downloadquery.recordcount#">

                         <cfset downloadquery.#arrTest# = Dateformat(downloadquery.#arrTest#,"mm/dd/yyyy")

          </cfloop>

</cfloop>

Problem comes in the bolded code. I m not able to have the value which is in arrTest.

How to do this.

Dynamically apply the column and replace the value

[Dan_Bracuk]

Most databases have functions that convert dates to strings in the format of your choice.  These are database specific and you didn't specify what you are using.  This approach is much simpler than what you are trying.  At most all you will have to do is prepend an apostrophe to your string so Excel doesn't mess it up.  You would have had to to this anyway.

Two comments about your query.  First, functions in the where clause slow down production.  Use parsedatetime to convert your string to a datetime object and use that.  Next, constraining on rownum <= 2000 seems odd, and could cause problems in the future.