Date Range problem: querying between dates

I'm trying to query between dates, and the date field in my access table is in a DateTime format as mm/dd/yyyy hh:mm:ss t

How do I prep the field in a query to just be mm/dd/yyyy?

I have a form that accepts a beginning and end date ( input='text' )

Here is my query

      <cfquery name='reporting'datasource='#db2#'>
            select * from #fcr# where fStatus='#reportstate#' and
            <cfif reportstate is 'Open'>fOpenDate
            <cfelseif reportstate is 'Approved'>fApprove='Yes' and fApproveDate
            <cfelseif reportstate is 'Disapproved'>fApprove='No' and fApproveDate
            <cfelseif reportstate is 'Applied'>fApplyDate
            <cfelseif reportstate is 'Reviewed'>dateformat(fReviewDate,'mm/dd/yyyy')
            </cfif> between
            <cfqueryparam cfsqltype="cf_sql_date" value="#start#"> and <cfqueryparam cfsqltype="cf_sql_date" value="#end#">
      </cfquery>

A user will enter a date as mm/dd/yyyy
firstbankakAsked:
Who is Participating?
 
Renante EnteraSenior PHP DeveloperCommented:
Hi firstbankak!

Let me revised your query.
--------------------------------------------------------------------------------------------------------------------------------------------------
  <cfquery name='reporting'datasource='#db2#'>
    select * from #fcr#
    where fStatus='#reportstate#' and
    <cfswitch expression="#reportstate#">
      <cfcase value="Open"> fOpenDate </cfcase>
      <cfcase value="Approved"> fApprove='Yes' and fApproveDate </cfcase>
      <cfcase value="Disapproved"> fApprove='No' and fApproveDate </cfcase>
      <cfcase value="Applied"> fApplyDate </cfcase>
      <cfcase value="Reviewed"> format(fReviewDate,'mm/dd/yyyy') </cfcase>
      <cfdefaultcase> 1=1</cfdefaultcase>
    </cfswitch>
    BETWEEN
    <cfqueryparam cfsqltype="cf_sql_date" value="#start#"> and <cfqueryparam cfsqltype="cf_sql_date" value="#end#">
  </cfquery
--------------------------------------------------------------------------------------------------------------------------------------------------
* Note : Use MSAccess function "format(date, [mask])" to set your desired mask for your date field/value.

I hope that this helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0
 
firstbankakAuthor Commented:
Thanks entrance2002!

<cfquery name='reporting'datasource='#db2#'>
    select * from #fcr#
    where fStatus='#reportstate#' and
    <cfswitch expression="#reportstate#">
      <cfcase value="Open"> format(fOpenDate,'mm/dd/yyyy') </cfcase>
      <cfcase value="Approved"> fApprove='Yes' and format(fApproveDate,'mm/dd/yyyy') </cfcase>
      <cfcase value="Disapproved"> fApprove='No' and format(fApproveDate,'mm/dd/yyyy') </cfcase>
      <cfcase value="Applied"> format(fApplyDate,'mm/dd/yyyy') </cfcase>
      <cfcase value="Reviewed"> format(fReviewDate,'mm/dd/yyyy') </cfcase>
      <cfdefaultcase> 1=1</cfdefaultcase>
    </cfswitch> BETWEEN #start# and #end#
</cfquery>

The <cfqueryparam...> was giving me an error, but this worked just fine, thanks again!
0
 
firstbankakAuthor Commented:
Interesting point, I am having query problems...

If I valid records with dates from 1/21/2006 to 4/30/2006, but if I query 1/21/2006 to 5/01/2006, it won't produce any results. If I use 5/01/06 as the end date, then it will produce the desired results?!

I'm having problems getting consistent results.

I reverting back to using this:

<cfparam name='startdate'default=''type='string'><cfparam name='enddate'default=''type='string'><cfparam name='reportstate'default=''>
<cfquery name='reporting'datasource='#db2#'>
    select * from #fcr#
    where fStatus='#reportstate#' and
    <cfswitch expression="#reportstate#">
      <cfcase value="Open"> format(fOpenDate,'mm/dd/yyyy') </cfcase>
      <cfcase value="Approved"> fApprove='Yes' and format(fApproveDate,'mm/dd/yyyy') </cfcase>
      <cfcase value="Disapproved"> fApprove='No' and format(fApproveDate,'mm/dd/yyyy') </cfcase>
      <cfcase value="Applied"> format(fApplyDate,'mm/dd/yyyy') </cfcase>
      <cfcase value="Reviewed"> format(fReviewDate,'mm/dd/yyyy') </cfcase>
      <cfdefaultcase> 1=1</cfdefaultcase>
    </cfswitch>
    BETWEEN
     <cfqueryparam cfsqltype="cf_sql_date" value="#startdate#"> and <cfqueryparam cfsqltype="cf_sql_date" value="#enddate#">
  </cfquery>

I get invalid date entry errors or queries with no results...

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.