[flexcoders] Query returning results based on date

0 views
Skip to first unread message

stinasius@yahoo.com [flexcoders]

unread,
Apr 20, 2015, 7:33:25 AM4/20/15
to flexc...@yahoogroups.com
 

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> 

 

__._,_.___
Posted by: stin...@yahoo.com
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (1)

.

__,_._,___

Paul Hastings paul.hastings@gmail.com [flexcoders]

unread,
Apr 20, 2015, 8:39:06 PM4/20/15
to flexc...@yahoogroups.com

On 4/20/2015 6:33 PM, stin...@yahoo.com [flexcoders] wrote:

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

well first off you don't need the query-of-query, you can get what you want in
one query.



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

what's datePaid look like? parseDateTime only works with en_US locale date formats.

in any case, have you tried testing this in plain cf first?

__._,_.___
Posted by: Paul Hastings <paul.h...@gmail.com>
.

__,_._,___

stinasius@yahoo.com [flexcoders]

unread,
Apr 21, 2015, 1:03:51 AM4/21/15
to flexc...@yahoogroups.com
 

when i try to hard code the date i get the correct result somewhat only that it misses a record. here is what i mean by hard coding


<cfquery name="qBal" datasource="bond100">
SELECT ID_biodata, quater, datePaid, amountPaid, manFees
FROM collections

WHERE datePaid <= DateValue('4/20/2015 0:00:00 AM')
            AND ID_biodata = 1
            AND quater = 'Quarter 1'

            ORDER BY datePaid
</cfquery>
        <cfquery dbtype="query" name="results">

        SELECT SUM(CAST(amountPaid as INTEGER) + CAST(manFees as INTEGER)) AS currentPaid
            FROM qBal            
        </cfquery>  

that bring's me closer to the right answer but i am comparing with different dates from the database so am stuck. 

__._,_.___
Posted by: stin...@yahoo.com
.

__,_._,___

stinasius@yahoo.com [flexcoders]

unread,
Apr 21, 2015, 9:46:47 AM4/21/15
to flexc...@yahoogroups.com
 

Ok, i have managed to make it work by formatting the date returned from the user interface in this format dd/mm/yyyy, which has worked only that some records are left out because in access the date field has time attached to it. so i was wondering how i can get rid of the time element in the date from the database.


i am using access as the database

__._,_.___
Posted by: stin...@yahoo.com
.

__,_._,___

stinasius@yahoo.com [flexcoders]

unread,
Apr 21, 2015, 10:43:15 AM4/21/15
to flexc...@yahoogroups.com
 

Any help guys? i don't understand what is happening but am getting a record short that is the query seems to be working only that its not returning the record of the datePaid, only records less than the datepaid. here is my query again


 <cfquery name="qBal" datasource="bond100">
SELECT ID_biodata, quater, datePaid, amountPaid, manFees
FROM collections

WHERE datePaid <= <cfqueryparam cfsqltype="cf_sql_date" value="#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>  

__._,_.___
Posted by: stin...@yahoo.com
.

__,_._,___
Reply all
Reply to author
Forward
0 new messages