Highlighted

filtering results based on dynamic date in coldfusion and access database

New Here ,
Apr 20, 2015

Copy link to clipboard

Copied

Hello Guys, i am using a cfquery and am trying to return results based on date. the idea is to return entries that are less than or equal to date but am not getting any results but when i change to greater than or equal to i get the all results in databse which is wrong.

Below is my code. is there something am doing wrong? Please help.

<cffunction name="getBal" access="remote">

<cfargument name="ID_biodata" type="string" required="true">

        <cfargument name="quater" type="string" required="true"/>

        <cfargument name="datePaid" type="date" required="true"/>

       

        <cfquery name="qBal" datasource="bond100">

SELECT ID_biodata, quater, datePaid, amountPaid, manFees

FROM collections

WHERE datePaid <= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#parseDateTime(arguments.datePaid)#">

            AND ID_biodata = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.ID_biodata#">

            AND quater = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.quater#">

            ORDER BY datePaid

</cfquery>

        <cfquery dbtype="query" name="results">

        SELECT SUM(CAST(amountPaid as INTEGER) + CAST(manFees as INTEGER)) AS totalPaid

            FROM qBal           

        </cfquery> 

        <cfreturn results />

    </cffunction>

TOPICS
Advanced techniques

Views

588

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

filtering results based on dynamic date in coldfusion and access database

New Here ,
Apr 20, 2015

Copy link to clipboard

Copied

Hello Guys, i am using a cfquery and am trying to return results based on date. the idea is to return entries that are less than or equal to date but am not getting any results but when i change to greater than or equal to i get the all results in databse which is wrong.

Below is my code. is there something am doing wrong? Please help.

<cffunction name="getBal" access="remote">

<cfargument name="ID_biodata" type="string" required="true">

        <cfargument name="quater" type="string" required="true"/>

        <cfargument name="datePaid" type="date" required="true"/>

       

        <cfquery name="qBal" datasource="bond100">

SELECT ID_biodata, quater, datePaid, amountPaid, manFees

FROM collections

WHERE datePaid <= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#parseDateTime(arguments.datePaid)#">

            AND ID_biodata = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.ID_biodata#">

            AND quater = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.quater#">

            ORDER BY datePaid

</cfquery>

        <cfquery dbtype="query" name="results">

        SELECT SUM(CAST(amountPaid as INTEGER) + CAST(manFees as INTEGER)) AS totalPaid

            FROM qBal           

        </cfquery> 

        <cfreturn results />

    </cffunction>

TOPICS
Advanced techniques

Views

589

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 20, 2015 0
Advocate ,
Apr 22, 2015

Copy link to clipboard

Copied

Turn on debugging so that you can see what ColdFusion is sending to your database server.

The debug info will show the SQL statement with query parameter values below it.

Cheers

Eddie

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Apr 22, 2015 0
BKBK LATEST
Adobe Community Professional ,
May 02, 2015

Copy link to clipboard

Copied

Do this just before the point where the function is called:

<cfdump var="#parseDateTime(datePaid)#"><cfabort>

Is that the date value you expect?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 02, 2015 0