Skip to main content
A
Anonymous
March 21, 2011
Answered

Dates Comparision in Coldfusion

  • March 21, 2011
  • 4 replies
  • 3269 views

Hi,


I have a problem with the date comparision in coldfusion code. I have followed the following technique for date comparision:

use <cfset> tag to change all the date values to one format:

       <cfset date1 = #CreateODBCDate(FORM.StartDate)#>
       <cfset date2 = #CreateODBCDate(FORM.EndDate)#>
       <cfset date = #CreateODBCDate(xx.enterdate)#>

Then i have done the comparion in <cfif> tag to return all the data in the database matching the criteria:

      <cfif (#DateCompare(date,date1)# NEQ -1) AND (#DateCompare(date,date2)# NEQ 1)>

Dates in between date1 and date2 should be returned.

I am not getting an error message but, my output is not correct. Please help.

Vijayvijay77.

    This topic has been closed for replies.
    Correct answer ilssac

    I modified the query and used ## around the dates. Its generating an error saying:

    Incorrect syntax near '09'.

    I dont understand. I appreciate your pateince with me.

    Vijayvijay77.


    vijayvijay77 wrote:

    I modified the query and used ## around the dates. Its generating an error saying:

    Incorrect syntax near '09'.

    I dont understand. I appreciate your pateince with me.

    Vijayvijay77.

    I would guess that '09' is the value of one of the form fields and '09' is not a DATE as a computer understands dates.

    But you would have a better understanding of what is going on if you knew what SQL statement was sent to the database.  This can be shown by turning on ColdFusion debuging or dumping the 'result' variable of the <cfquery...> statement.  It may also be included in the error message.  It would be included in the <cfcatch...> structure if you where to put a <cftry>...<cfcatch></cfcatch></cftry> block around your query.

    4 replies

    R
    Reed_Powell-ttnmOb
    Inspiring
    March 21, 2011

    Try removing the createODBCDate() function calls and see if that helps.  You want the dates that you are comparing to be stored as valid CF format dates, which is pretty liberal but I don't think that ODBC date formats are legal.  Your later example using DateFormat() should have worked, so I would try debugging that code by dumping out the variables and confirming that they contain what you think they do.  If you decide to follow the advice of putting the date range checks into your query (which may or may not make sense, depending on what your applications might also be doing with that query's data), then you will want to use the createODBCdate() function at that point.

    You might want to use the isDate() function at the top of your code to verify that what the person typed into the form fields is a valid date, otherwise you will get errors like the one you got if they type in something CF cannot convert to a date.

    Let us know how the dumping of the variables goes if you continue to have problems.

    -reed

    A
    Anonymous
    March 21, 2011

    Hi,

    <cfquery name="yy" datasource="dd">
                 Select name,enteredOn,entryid
                From kk
                where  enteredOn BETWEEN <cfqueryparam value="#FORM.StartDate#" cfsqltype="cf_sql_date"> AND <cfqueryparam value="#FORM.EndDate#" cfsqltype="cf_sql_date">
             </cfquery>

    <cfoutput>

    Outputing all the relevant data!

    </cfoutput>

    These gives me all the data from the database without any error. Am I making a mistake in the where clause?

    Thanks everyone for all the valuable suggestions, I am learning Coldfusion in each and every step I am going through.

    Vijayvijay77.

    D
    Dan_Bracuk
    Inspiring
    March 21, 2011

    It's ok but there are two ways to improve it.

    First, input validation has been mentioned at least once in this thread.  If you are not doing this already, you should.  Saying what happened between date1 and date2 is one of my most common task.  My validation is to:

    1.  Make sure both form fields really are dates

    2.  Make sure they are in the right order.

    3.  Make sure they are not too far apart.

    4.  Make sure they don't exceed specified minimums and maximums.

    I wrote a custom tag to do this, so all I do is call the custom tag.  The custom tag also creates Date Objects that I use in my query instead of the form fields, which are strings.  What you are doing works, but ColdFusion has a history of getting stricter about things like this as it gets upgraded, so my way is safer.

    Second, most db datefields are actually datetime fields.  If yours is one of those, and you are storing the time portion as well, you might want to change your where clause from:

    where the date field between date1 and date2

    to

    where the date field >= date 1 and the datefiled < the day after date2

    D
    Dan_Bracuk
    Inspiring
    March 21, 2011

    You are off to a good start by converting the form values to actual date objects.  As the others have hinted, using those variables in a where clause of your query is a much better approach than what you appear to be attempting.

    Owainnorth
    Owainnorth
    Inspiring
    March 21, 2011

    Seriously now.

    It's logic. Therefore fix the problem with a logical approach rather than just giving up. Here's what I would do:

    For each variable you're working with, output it to the screen. For each one, also output #DateCompare(date,date1)# NEQ -1# and #DateCompare(date,date2)# NEQ 1#. You'll get a true or false for each.

    That way you can see exactly where your error is, or at least you'll know where to start from.

    ilssac
    ilssac
    Inspiring
    March 21, 2011

    Showing a little more code would probably help.

    BUT

    vijayvijay77 wrote:

    Then i have done the comparion in <cfif> tag to return all the data in the database matching the criteria:

    This strongly hints that you are using CFML functions inside of a database SQL query when you probably should be using SQL functions that match your database.

    A
    Anonymous
    March 21, 2011

    Hi ilssac,

    Thanks for your valuable suggestions. This is my psuedo code:

    <cfquery> -----querying all the data in the database with all entries.

    <cfset> statements setting up all the dates in proper format to be compared:

         <cfset date1 = #DateFormat(FORM.StartDate,"mm/dd/yyyy")#>
           <cfset date2 = #DateFormat(FORM.EndDate,"mm/dd/yyyy")#>
           <cfset date = #DateFormat(yy.enterdate,"mm/dd/yyyy")#>

    A
    Anonymous
    March 21, 2011

    Sorry, the code contin ues as:

    Then i have done the comparion in <cfif> tag to return all the data in the database matching the criteria:

          <cfif (#DateCompare(date,date1)# NEQ -1) AND (#DateCompare(date,date2)# NEQ 1)>

                   [Dates in between date1 and date2 should be returned.]

         <cfoutput>  output the relevant data from the database.

    That is the flow of all my code.

    As suggested by Mr.North, I have put a <cfoutput> tag just after the <cfset> statements, and I am getting the dates in mm/dd/yyyy format.

    But when I am comparing the dates using DateCompare(), coldfusion function, not getting the correct output without any error messages.

    Thanks for your valuable feedback.

    Vijayvijay77.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices