Skip to main content
E
Eric_Dahlenburg
Inspiring
June 22, 2009
Question

Adding Oracle Number and Date types for dynamic display

  • June 22, 2009
  • 1 reply
  • 970 views

Hi,

In a nutshell, I am adding the warranty period called Warranty_days retreived as a "Number" data type from Oracle and adding it to

a Date called Purchase_Date which is stored as a DATE datatype in the format dd-mon-yy in Oracle.

Typical data might be: Warranty_days (30, 60 ,90, 180, etc)  Purchase_date (01-JUN-09, 24-AUG-09, etc.)

I attempted to add and display as part of a table like this:

<td width="114" class="style1"><span class="style1">#Warranty_Results.Warranty_Days + Warranty_Results.Purchase_Date#</span></td>

Which results in a number. I beleive this number actually represents a date, but cannot cast it to the 23-JUN-09 format as part of dynamic text in the table.

Originally, I had this working off an Access database using  DATEFORMAT: #DateFormat( DateAdd("d", Warranty_Results.Warranty_Days, Warranty_Results.Purchase_Date), "mm-dd-yyyy")#

But you can't use DATEFORMAT with Oracle.   Any ideas?

    This topic has been closed for replies.

    1 reply

    D
    Dan_Bracuk
    Inspiring
    June 22, 2009

    Use to_char to format the result of your date math.  I'm not sure, but the format you want might actually be the default.  If not, use the appropriate mask.

    And, just so you know, date datatypes are not stored in any format in any database.  They are essentially numbers.  The formats you see are default display properties.

    E
    Eric_DahlenburgAuthor
    Inspiring
    June 22, 2009

    I know how to use to-char if I am writing SQL query, but how do I use it inside the table?

    Thanks,

    Eric

    Ps     Here is what the rest of the table looks like:

    <table width="820" border="1" cellpadding="3" cellspacing="1" bgcolor="D9FFFF">
        <cfoutput query="Warranty_Results">
      <cfif Issue_Log.currentrow mod 1 eq 0 >
      <td class="style1"><tr>
      </cfif>
          <p class="style1 style1">
          <td class="style1"><tr>
          <td class="style1"></p>
          <td width="146" class="style1"><span class="style1">#Warranty_Results.Part_Number#</span></td>
          <td width="169" class="style1"><span class="style1">#Warranty_Results.Serial_Number#</span></td>
          <td width="140" class="style1"><span class="style1">#Warranty_Results.Vendor_Name#</span></td>
          <td width="114" class="style1"><span class="style1">#Warranty_Results.Warranty_Days + Warranty_Results.Purchase_Date#</span></td>
          <td width="183" class="style1"><span class="style1">#Warranty_Results.Reference#</span></td>
       <cfif Warranty_Results.currentrow mod 1 eq 0 >
         <p class="style1 style1">
            <td class="style1"></tr>
         <td class="style1"></p>
      </cfif>
        </cfoutput>
      </table>

    D
    Dan_Bracuk
    Inspiring
    June 22, 2009

    Use it in the query and output the result that Oracle gives you.

    or

    Select the 2 fields and use Cold Fusion dateadd and dateformat.  I prefer to_char myself.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices