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?
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
1
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")#
1

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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
Answer is the same:  Use either TO_CHAR at the database level or #dateFormat at the cold fusion level.
0
QuackdeveloperAuthor Commented:
worked...thanks
0
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

0
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.
0
QuackdeveloperAuthor Commented:
I have...thnx
0
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?
0
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.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.