try creating date objects first using <cfset yourdate = createdate(year,month,day)>
then supply dates to your queries using
<cfqueryparam cfsqltype="CF_SQL_DATE" value="#createODBCdate(your_date)#">
This should make the sql db independant (probably!)
HTH
On Wed, 22 Dec 2004 09:41:54 -0700, Jason Smith
<[EMAIL PROTECTED]> wrote:
> The following code is from a menu on a site that shows the
> monthly/quarterly/yearly goal and sales the goal part is working fine but
> since switching from access to mysql the currently month/quarter/year sales
> isn't being calulated.
>
> <!--- RIGHT MENU --->
> <td>
> <table class="rightMenu">
> <tr>
> <td class="rightMenu"
> style="text-align: left;"><br><br>
> <cfset theMonth =
> datePart("m", now())>
> <cfset theYear =
> datePart("yyyy", now())>
> <cfset theDay = datePart("d",
> now())>
> <cfquery
> name="getMonthlyPurchase" datasource="#DS#"
> cachedwithin="#CreateTimeSpan(0,4,35,0)#">
> SELECT
> Sum(tblItemPurchase.itemPurchasePrice) AS totalMonthlyPurchase
> FROM tblItemPurchase
> WHERE
> tblItemPurchase.itemPurchaseDate >= '#theMonth#/#1#/#theYear#'
> </cfquery>
> <cfset theGoal = 175000>
> <cfif
> getMonthlyPurchase.recordCount neq 0 and
> getMonthlyPurchase.recordCount neq "" and
> getMonthlyPurchase.totalMonthlyPurchase neq "">
> <cfset amountLeft =
> theGoal - getMonthlyPurchase.totalMonthlyPurchase>
> <cfelse>
> <cfset amountLeft =
> theGoal>
> </cfif>
> <p><table width="100%"><tr><td
> bgcolor="#CCCCCC" style="font-size:
> 18px; font-weight: bold; text-align: center; padding: 2 3 2 3;
> border-right: black 1px solid; border-bottom:black 1px
> solid;">GOALS</td></tr></table>
> <table
> style="width:100%; "><tr><td style="border: dashed gray 1px;
> padding: 2 3 2 3; font-size: 12px; width:100%;">
> <strong>THIS
> MONTH:</strong>
> <table
> bgcolor="#B9E1F4" width="100%">
> <tr>
>
> <td style="width:100%; ">
>
> <span
> class="voTitle">GOAL:<cfoutput>#DollarFormat(theGoal)#</cfoutput>.</span><span
> style="font-size: 2px;"><br><br></span>
>
> <span style="font-size: 10px; font-weight: bold;"><strong>SO
> FAR:<cfoutput>#DollarFormat(getMonthlyPurchase.totalMonthlyPurchase)#</cfoutput></strong></span><span
> style="font-size: 2px;"><br><br></span>
>
> <span class="adminTitle">TO
> GO:<cfoutput>#DollarFormat(amountLeft)#</cfoutput></span>
>
> </td>
> </tr>
> </table>
> </td>
> </tr>
> <tr>
> <td style="font-size:
> 2px;"> </td>
> </tr>
> </table>
>
> <cfset theMonth =
> datePart("m", now())>
> <cfset theYear =
> datePart("yyyy", now())>
> <cfswitch
> expression="#theMonth#">
> <cfcase value="1,2,3">
> <cfset
> startMonth = 1>
> <cfset
> endMonth = 3>
> <cfset endDay
> = 31>
> </cfcase>
> <cfcase value="4,5,6">
> <cfset
> startMonth = 4>
> <cfset
> endMonth = 6>
> <cfset endDay
> = 30>
> </cfcase>
> <cfcase value="7,8,9">
> <cfset
> startMonth = 7>
> <cfset
> endMonth = 9>
> <cfset endDay
> = 30>
> </cfcase>
> <cfcase
> value="10,11,12">
> <cfset
> startMonth = 10>
> <cfset
> endMonth = 12>
> <cfset endDay
> = 31>
> </cfcase>
> </cfswitch>
> <cfquery
> name="getQuarterlyPurchase" datasource="#DS#"
> cachedwithin="#CreateTimeSpan(0,4,35,0)#">
> SELECT
> Sum(tblItemPurchase.itemPurchasePrice) AS totalMonthlyPurchase
> FROM tblItemPurchase
> WHERE
> tblItemPurchase.itemPurchaseDate >= '#startMonth#/1/#theYear#'
> AND
> tblItemPurchase.itemPurchaseDate <= '#endMonth#/#endDay#/#theYear#'
> </cfquery>
> <cfset theGoal = 500000>
> <cfif
> getQuarterlyPurchase.recordCount neq 0 and
> getQuarterlyPurchase.recordCount neq "" and
> getQuarterlyPurchase.totalMonthlyPurchase neq "">
> <cfset amountLeft =
> theGoal - getQuarterlyPurchase.totalMonthlyPurchase>
> <cfelse>
> <cfset amountLeft =
> theGoal>
> </cfif>
> <table width="100%"><tr><td
> style="border: dashed gray 1px; padding:
> 2 3 2 3; font-size: 12px;">
> <strong>THIS
> QUARTER:</strong>
> <table
> bgcolor="#9AD1EF" width="100%">
> <tr>
>
> <td>
>
> <span
> class="voTitle">GOAL:<cfoutput>#DollarFormat(theGoal)#</cfoutput>.</span><span
> style="font-size: 2px;"><br><br></span>
>
> <span style="font-size: 10px; font-weight:
> bold;"><strong>SO FAR:<cfoutput>#DollarFormat(getQuarterlyPurchase.totalMonthlyPurchase)#</cfoutput></strong></span><span
> style="font-size: 2px;"><br><br></span>
>
> <span class="adminTitle">TO
> GO:<cfoutput>#DollarFormat(amountLeft)#</cfoutput></span>
>
> </td>
> </tr>
> </table>
> </td>
> </tr>
> </table>
>
> <cfset theYear =
> datePart("yyyy", now())>
> <cfquery
> name="getYearlyPurchase" datasource="#DS#"
> cachedwithin="#CreateTimeSpan(0,4,35,0)#">
> SELECT
> Sum(tblItemPurchase.itemPurchasePrice) AS totalMonthlyPurchase
> FROM tblItemPurchase
> WHERE
> tblItemPurchase.itemPurchaseDate >= '1/1/#theYear#'
> AND
> tblItemPurchase.itemPurchaseDate <= '12/31/#theYear#'
> </cfquery>
> <cfset theGoal = 2000000>
> <cfif
> getYearlyPurchase.recordCount neq 0 and
> getYearlyPurchase.recordCount neq "" and
> getYearlyPurchase.totalMonthlyPurchase neq "">
> <cfset amountLeft =
> theGoal - getYearlyPurchase.totalMonthlyPurchase>
> <cfelse>
> <cfset amountLeft =
> theGoal>
> </cfif>
> <table width="100%"><tr><td
> style="border: dashed gray 1px; padding:
> 2 3 2 3; font-size: 12px;">
> <strong>THIS
> YEAR:</strong>
> <table
> bgcolor="#57B4E6" width="100%">
> <tr>
>
> <td>
>
> <span
> class="voTitle">GOAL:<cfoutput>#DollarFormat(theGoal)#</cfoutput>.</span><span
> style="font-size: 2px;"><br><br></span>
>
> <span style="font-size: 10px; font-weight:
> bold;"><strong>SO FAR:<cfoutput>#DollarFormat(getYearlyPurchase.totalMonthlyPurchase)#</cfoutput></strong></span><span
> style="font-size: 2px;"><br><br></span>
>
> <span class="adminTitle">TO
> GO:<cfoutput>#DollarFormat(amountLeft)#</cfoutput></span>
>
> </td>
> </tr>
> </table>
> </td>
> </tr>
> </table>
>
> <cfquery name="getAllTime"
> datasource="#DS#"
> cachedwithin="#CreateTimeSpan(0,4,35,0)#">
> SELECT
> Sum(tblItemPurchase.itemPurchasePrice) AS totalMonthlyPurchase
> FROM tblItemPurchase
> </cfquery>
> <table width="100%"><tr><td
> style="border: dashed gray 1px; padding:
> 2 3 2 3; font-size: 12px;">
> <strong>ALL
> TIME:</strong>
> <table
> bgcolor="#2195D3" width="100%">
> <tr>
>
> <td>
>
> <span style="font-size: 10px; font-weight: bold;
> color:#FFFFFF;"><strong>SALES:
> <cfoutput>#DollarFormat(getAllTime.totalMonthlyPurchase)#</cfoutput></strong></span><span
> style="font-size: 2px;"><br><br></span>
>
> </td>
> </tr>
> </table>
> </td>
> </tr>
> </table>
>
> <!--- <table style="width:100%; ">
> <tr>
> <td style="font-size:
> 2px;"> </td>
> </tr>
> <cfset theGoal =
> dateDiff('d', now(), '19/04/2004')>
> <tr>
> <td style="border:
> dashed gray 1px; padding: 2 3 2 3; font-size: 12px;">
> <strong>SIX
> MONTH</strong>
> <table
> bgcolor="#FFCC33" style="border: solid #FF9933 2px;"
> width="100%">
>
> <tr>
>
> <td>
>
> <span class="voTitle">GOAL: $250,750.00</span><span
> style="font-size: 2px;"><br><br></span>
>
> <span style="font-size: 10px; font-weight:
> bold;"><strong>DUE:
> 04/19/04.</strong></span><span style="font-size: 2px;"><br><br></span>
>
> <span class="adminTitle">DAYS LEFT:
> <cfoutput>#theGoal#</cfoutput>.</span>
>
> </td>
>
> </tr>
>
> </table>
> </td></tr></table>
> --->
> </p>
> </td>
> </tr>
> </table>
> </td>
> <!--- END RIGHT MENU --->
>
> Anyone that might be familiar with what needs to be changed to make this
> query work in mysql not just access?
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Special thanks to the CF Community Suite Silver Sponsor - CFDynamics
http://www.cfdynamics.com
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188562
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
