Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Date Range problem: querying between dates

Posted on 2006-05-01
3
Medium Priority
?
648 Views
Last Modified: 2013-12-24
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
0
Comment
Question by:firstbankak
  • 2
3 Comments
 
LVL 14

Accepted Solution

by:
Renante Entera earned 2000 total points
ID: 16582537
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
 

Author Comment

by:firstbankak
ID: 16587917
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
 

Author Comment

by:firstbankak
ID: 16588531
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

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Loops Section Overview
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question