Answered
CFQUERY - comparing same dates?
I'm trying to query a range of dates from my database with
<cfquery>. I have two variables set to fromDate and toDate
which are input values from a FORM (user determines date range).
<cfset start_date = #CREATEODBCDATE(fromDate)#>
<cfset end_date = #CREATEODBCDATE(toDate)#>
My query looks like this:
<cfquery datasource="myds">
SELECT dbID, dbDate
FROM myTable
WHERE dbDate BETWEEN #start_date# AND #end_date#
</cfquery>
This works for every value except when the dbDate is the same as the end_date. There's a problem with TIME. dbDate in my database is of type datetime and holds both a date and time value (ex: 9-24-2008 9:03:58 AM). But the start_date and end_date do not have a time specified from the FORM, so they both display with 12:00:00 AM at the end. So lets say from my FORM, I choose 9-24-2008 for both my start and ending dates. In my query, it looks like...
dbDate BETWEEN 9-24-2008 12:00:00 AM AND 9-24-2008 12:00:00 AM
So if my dbDate's time is anything but 12:00:00 AM, it will not catch it in the comparison. I have to choose 9-25-2008 (9-25-2008 12:00:00 AM) to catch any dates on the 24th (and again, any dates in my database that are 9-25-2008 will now be discluded).
How can write this so the times of the dates are irrelevant? Is there a way to strip the time from dbDate? or to set the time of end_date so it looks like 9-24-2008 11:59:59 PM ? Any help is appreciated.
<cfset start_date = #CREATEODBCDATE(fromDate)#>
<cfset end_date = #CREATEODBCDATE(toDate)#>
My query looks like this:
<cfquery datasource="myds">
SELECT dbID, dbDate
FROM myTable
WHERE dbDate BETWEEN #start_date# AND #end_date#
</cfquery>
This works for every value except when the dbDate is the same as the end_date. There's a problem with TIME. dbDate in my database is of type datetime and holds both a date and time value (ex: 9-24-2008 9:03:58 AM). But the start_date and end_date do not have a time specified from the FORM, so they both display with 12:00:00 AM at the end. So lets say from my FORM, I choose 9-24-2008 for both my start and ending dates. In my query, it looks like...
dbDate BETWEEN 9-24-2008 12:00:00 AM AND 9-24-2008 12:00:00 AM
So if my dbDate's time is anything but 12:00:00 AM, it will not catch it in the comparison. I have to choose 9-25-2008 (9-25-2008 12:00:00 AM) to catch any dates on the 24th (and again, any dates in my database that are 9-25-2008 will now be discluded).
How can write this so the times of the dates are irrelevant? Is there a way to strip the time from dbDate? or to set the time of end_date so it looks like 9-24-2008 11:59:59 PM ? Any help is appreciated.
AdChoices