troubleshooting Question

DateAdd Function in Coldfusion

Avatar of amnth
amnth asked on
ColdFusion Language
11 Comments1 Solution2267 ViewsLast Modified:
I have a problem, and need some help badly. I have adatabase. what I need is run the CFM page and get the latest data added that is yesterdays entered data from the database and email it to me.I have set a scheduled task to run the page.
When I run the page with this code it gives all of the new records added.But I need only yesterdays.
 Can someone help?
<cfset myDate = now() - 1>
<CFQUERY NAME="getNew" DATASOURCE="NewSuppliers">
SELECT DocketNumber, SupplierName, FirstName, LastName, FirstName2, LastName2, Address, Address2, City, State, ZipCode, Phone, TollFree, Fax, Email, Website, DateAdded, typeofSupplier
FROM Suppliers
WHERE  DateAdded < #createODBCDate(myDate)#
</CFQUERY>
<CFQUERY NAME="getchangeRpt" DATASOURCE="NewSuppliers">
SELECT *
FROM CF_HistorybyFieldQry
WHERE DateofChange < #createODBCDate(myDate)#

ORDER BY DateofChange DESC
</CFQUERY>

<cfif getchangeRpt.RecordCount GT 0>
   <cfmail to="me@me.com"
 cc=""  
    bcc=""
        from="mm@mm.mm.mm.mm"
        subject="Report of Changes to Suppliers Database"
        server="111.111.111.111"
        port=25
        timeout=60
        type="HTML">
<B></b>
<Left>
<CENTER>
<TR>
<P><b><Center>Changes</center></b>

  <table width="100%" border="1" align="CENTER" bordercolor="Black">
    <TR>
      <TD width="110"><b>Docket Number</b></TD>
      <TD align="center" width="226"><b>Supplier Name</b></TD>
      <TD align="center" width="114"><b>Utility Type</b></TD>
      <TD width="76" align="center"><b><i>Change</i></b></TD>
      <TD width="96" align="center"><b>Field name</b></TD>
      <TD align="center" width="107"><b>Date of<br>
Change</b></TD>
    </TR>
<cfloop query="getchangeRpt">
    <TR>
        <TD VALIGN="TOP">#DocketNumber#</TD>
      <TD VALIGN="TOP"><cfif Len(ElectricSupplierName) EQ 0>#GasSupplierName#<cfelse>#ElectricSupplierName#</cfif></TD>
      <TD VALIGN="TOP">
        <cfif Len(ElectricSupplierName) EQ 0>
        Gas
        <cfelse>
        Electric
        </cfif></TD>
      <TD VALIGN="TOP"><b>Old Data:</b>  #oldData#&nbsp;<br>
                        <b>New Data:</b> #newData#&nbsp;</TD>
      <TD VALIGN="TOP">#Fname#</TD>
      <TD VALIGN="TOP">#DateFormat(DateofChange, 'mm/dd/yy')#</TD>
    </TR>
</cfloop>
  </TABLE>
</cfmail>
</cfif>
<cfif getNew.RecordCount GT 0>
<cfmail to="me@me.com"
cc=""  
    bcc=""
        from=""
        subject="Report of Changes to Suppliers Database"
        server=" "
        port=25
        timeout=60
        type="HTML">
<B></b>
<CENTER>
<TABLE BORDER=1 >
<TR>

<P><b><Center>New Companies</center></b>

      <cfloop query="getNew">       
      <table border="0" align="CENTER">
    <TR>
      <TD COLSPAN="3" BGCOLOR="Silver"><b>#DocketNumber#&nbsp;&nbsp;&nbsp;#SupplierName#</b></TD>
    </TR>
    <TR>
        <TD VALIGN="TOP" width="146">Name: <br>
        Alternate Contact: <br>
          Address: </TD>
        <TD VALIGN="TOP" width="216">#FirstName# #LastName#<br>
        #FirstName2# #LastName2#<br>
        #Address#<br>
        <cfif Len(Address2) gt 0>
          #Address2#<br>
        </cfif>
        #City#, #State#  #ZipCode#</TD>
    </TR>
    <tr>
      <TD VALIGN="TOP">Telephone:<br>
        Alternate Telephone:<br>
        Fax:<br>
        Email:<br>
        Website:
        </TD>
        <TD VALIGN="TOP">#Phone#<BR>
        #Tollfree#<br>
        #Fax#<br>
        #Email#<br>
        #Website#
        </TD>
    </TR>
  <tr>
  <td>Date Added:</td>
  <td>#DateFormat(DateAdded,'mm/dd/yy')#</td>
  </tr>
      </TABLE>
</cfloop>
</cfmail>
</cfif>


ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros