Re: [flexcoders] Query returning results based on date
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
Re: [flexcoders] Query returning results based on date
Ok, i have managed to make it work by formatting the date returned from the user interface in this format dd/mm/, 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
Re: [flexcoders] Query returning results based on date
On 4/20/2015 6:33 PM, stinas...@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?
Re: [flexcoders] Query returning results based on date
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.
[flexcoders] Query returning results based on date
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
