Cold Fusion output isn't returning dates correct from a sql query.

I'm having output issues with a cold fusion file using a sql query. The code for the file is:

<cfset today = DateFormat("#Now()#","mm/dd/yyyy")> 
<cfset time = TimeFormat("#Now()#","hh:mm:sstt")>
<cfset #dsn# = "prod1_r">
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>

<cfquery name="queueData" datasource="#dsn#">
Select 
     distinct q.queue_name, 
     INV.FIFO_FLAG, 
     WI.RETRIEVING_USER_ID,
       decode(TO_NUMBER(inv.discount_terms_days), null, 
              inv.document_receipt_date + inv.discount_terms_net, 0, 
              inv.document_receipt_date + inv.discount_terms_net, 
              inv.invoice_date + inv.discount_terms_days) due_date, 
       inv.document_id,
       inv.invoice_num, 
       inv.material_amount, 
       lpad(TO_NUMBER(inv.discount_terms_percentage),5,0) percent,
       TO_NUMBER(inv.discount_terms_days) days,
       TO_NUMBER(inv.discount_terms_net) net,
       TO_DATE(wpv.property_value,'DD-MON-YYYY')  FINCENDT,
       inv.markview_document_id markview,
       wi.workitem_instance_id workitem, 
       trunc(wi.enqueued_timestamp) Enter_que
  From sf_workitem_instance wi,
       sf_workitem_property_value wpv,
       wins_invoices inv,
       sf_queue q
where wi.queue_id = q.queue_id 
   and wpv.WORKITEM_INSTANCE_ID = wi.WORKITEM_INSTANCE_ID
   and inv.workitem_instance_id = wi.workitem_instance_id
   and wpv.WORKITEM_CLASS_PROPERTY_ID in (1046,187)
   and wi.queue_id not in (120,14,40)
   and q.queue_name <> 'Associated'
   and q.queue_name <> 'Not Associated'
order by 1,2,3,4,5,6,7,8
</cfquery>

<cfset MaxRows_queueData=100>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML LANG="EN-US">

<head>
<title>Detail Report</title>

<script language="JavaScript" type="text/JavaScript">
function MM_reloadPage(init) {  //reloads the window if Nav4 resized
  if (init==true) with (navigator) {if ((appName=="Netscape")&&(parseInt(appVersion)==4)) {
    document.MM_pgW=innerWidth; document.MM_pgH=innerHeight; onresize=MM_reloadPage; }}
  else if (innerWidth!=document.MM_pgW || innerHeight!=document.MM_pgH) location.reload();
}
MM_reloadPage(true);
</script>

<link href="../css/home.css" rel="stylesheet" type="text/css" />
</head>

<BODY>
<cfif queueData.RecordCount EQ 0>
  <div id="errormessage" style="position:absolute; left:232px; top:151px; width:368px; height:27px; z-index:20; visibility: visible;" class="alert"> 
    Sorry, no matching records were found. Please try again. </div>
</cfif>
<table width="100%" height="100%" border="0" cellpadding="0" cellspacing="0" background="../images/grad2b.gif">
   <tr>
      <td width="10%">&nbsp;</td>
      <td width="80%" valign="top">
	     <table width="100%" border="0" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF" >
            <tr>
               <td><a href="../index.htm"><img src="../images/IntranetBanner_010909.jpg" alt="Intranet banner" width="800" height="82" border="0" ></a></td>
            </tr>		
            <tr>
               <td><CF_topmenu_new></td>
            </tr>
            <tr>
               <td valign="top"><table width="100%" border="0" align="center">
                  <tr>
                     <td valign="bottom" class="Headline18Gold"> Detail Report</td>
                  </tr>
                  <tr>
                     <td class="maintxt12">&nbsp;</td>
                  </tr>			  
				  <tr>
				    <td>
      <table width="100%" border="0">
		<tr>
		  <td colspan="2">

<table width="100%" align="center" border="0">
	<tr class="subcathead">              
			  <td width="45" class="subcathead">Queue Name</td>
			  <td width="40" height="41" class="subcathead">Fifo Flag</td>
			  <td width="53" class="subcathead">User ID</td>
			  <td width="53" class="subcathead">Due<br>Date</td>
			  <td>Document ID</td>
			  <td>Invoice<br>Number</td> 
			  <td>Material<br>Amount</td>
			  <td>Disc<br>Terms<br>Percent</td>
              <td>Disc<br>Terms<br>Days</td>
              <td>Disc<br>Terms<br>Net</td>
			  <td>Property Value</td>
			  <td>Work<br>Item<br>Instance<br>ID</td>
			  <td>Category</td>
			  <td>Date<br>Entered<br>Queue</td>
		       </tr>

		 <cfif queueData.RecordCount GT 0>	        
		   <cfoutput query="queueData">
			   <tr class="NavContentGrayBg">
			  <td>#queue_name#</td>
			  <td>#fifo_flag#</td>
			  <td>#RETRIEVING_USER_ID#</td>
			  <td>#due_date#</td>
			  <td>#document_id#</td>
			  <td>#invoice_num#</td>
			  <td align="right">#dollarFormat(material_amount)#</td>
              <td align="right">#percent#</td>
              <td align="right">#days#</td>
              <td align="right">#net#</td>
              <td align="right">#FINCENDT#</td>
			  <td>#markview#</td>
			  <td>#workitem#</td>
			  <td>#enter_que#</td>
			   </tr>
		  </cfoutput>		       
		</cfif>      
           </table>           
        </td>
        </tr>
        <tr>
          <td>&nbsp;</td>
        </tr>
       </table>                    
       </td>
	   </tr>                
          </table>
           </td>
            </tr>
			<tr>
               <td>&nbsp;</td>
            </tr>
            
            <tr>
               <td><CF_CGfooter_new></td>
            </tr>
            
      </table></td>
      <td width="10%">&nbsp;</td>
   </tr>
   <tr>
      <td width="10%">&nbsp;</td>
      <td width="80%">&nbsp;</td>
      <td width="10%">&nbsp;</td>
   </tr>
   
</table>

</body>
</html>

Open in new window


The query works and returns correct data but the dates aren't truncated the way it should be w/in the html report that is returned. (mm/dd.yy is what is desired) It's truncating correctly in a sql output program such as TOAD but not when ran in the browser. Can anyone shed light on why this isn't setting the date per the query script?
QuackdeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Which columns are you concerned with?

What is the actual data type of wpv.property_value?  If it is a string, then TO_DATE is correct.  If it is already a date, then you don't need TO_DATE.

In Oracle dates don't have a 'format' until they are queried.  Each tool has it's own 'default' way to display them.

If you want a specific format, best to use explicit data type conversion to convert the date to a string.

You do that with TO_CHAR.

For example: to_char(sysdate,'MM/DD/YY')

Since this is the only date I know about, it goes like:
TO_CHAR(TO_DATE(wpv.property_value,'DD-MON-YYYY'),'MM/DD/YY')  FINCENDT
gdemariaCommented:
In coldfusion, you can format the date using dateFormat()

Allow your query to just return the raw date and then format it using coldfusion..

#dateFormat(querData.FINCENDT,"mm/dd/yyyy")#

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
QuackdeveloperAuthor Commented:
the Due Date and the enter que date are the columns I'm having issue/s with.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
Answer is the same:  Use either TO_CHAR at the database level or #dateFormat at the cold fusion level.
QuackdeveloperAuthor Commented:
worked...thanks
QuackdeveloperAuthor Commented:
Will this work with an excel output? I'm getting an error when I try to use it the same way See the enter_que output below:

<cfoutput> 

#cellbeg# Queue Name #cellend# #cellbeg# Fifo Flag #cellend# #cellbeg# User ID #cellend# #cellbeg# Due Date #cellend# #cellbeg# Document ID#cellend# #cellbeg# Invoice Number #cellend# #cellbeg# Material Amount #cellend# #cellbeg# Discount Terms Percent #cellend# #cellbeg# Discount Terms Days #cellend# #cellbeg# Discount Terms Net #cellend# #cellbeg# FINCENDT #cellend# #cellbeg# Markview #cellend# #cellbeg# Work Item #cellend# #cellbeg# Date Entered Queue #cellend# 

</cfoutput> </font>
</tr>

<cfoutput query="queueData">
#begline#
#cellbeg1# &nbsp;#queueData.queue_name# #cellend# #cellbeg1# &nbsp;#fifo_flag# #cellend# #cellbeg1# &nbsp;#RETRIEVING_USER_ID# #cellend# #cellbeg1# &nbsp;#queueData.due_date# #cellend# #cellbeg1# &nbsp;#queueData.document_id#  #cellend# #cellbeg1# &nbsp;#queueData.invoice_num# #cellend# #cellbeg1# &nbsp;#queueData.material_amount# #cellend# #cellbeg1# &nbsp;#queueData.percent#  #cellend# #cellbeg1# &nbsp;#queueData.days# #cellend# #cellbeg1# &nbsp;#queueData.net# #cellend# #cellbeg1# #queueData.FINCENDT# #cellend# #cellbeg1# #queueData.markview# #cellbeg1# #queueData.workitem#  #cellbeg1# #queueData.dateFormat(enter_que,"mm/dd/yyyy")# 
#endline#
</cfoutput> 

Open in new window

slightwv (䄆 Netminder) Commented:
I'm not a Cold Fusion person and don't know how to get it into Excel.  I would suggest you open a new question for this so Cold Fusion Experts will see it.
QuackdeveloperAuthor Commented:
I have...thnx
QuackdeveloperAuthor Commented:
could I use the TO_CHAR option and rewrite the query a bit? Not sure how to add that w/o messing the current query structure up?
slightwv (䄆 Netminder) Commented:
If you use TO_CHAR Oracle will return it as a string.  I have no way of knowing what that does to your Cold Fusion code.

Copy off what you have and give it a try.  It should either work or not.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.