Dates Comparision in Coldfusion

[Guest]

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.

[ilssac]

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.

[Guest]

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")#>

[Guest]

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.

[Owainnorth]

So hang on, you're not saying that you're doing a SELECT * from the database with no WHERE clauses, then only displaying the relevant rows...?

[Guest]

Hi Owain North,

Yes, I am quering the database without any where clauses to get all the relevant cloumns from the database, then writing the <cfif> statements to restrict the output to just the rows matching the criteria.

Thanks.

[Guest]

Hi Owain North,

You are getting me wrong Sir, I am ending the initial query, then writing the cfif statements. Not writing them inside the cfquery.

With all due respect, Thank you for all the suggestion and your valuable time, I dont expect any future suggestions from you!!

Vijayvijay77.

[ilssac]

FIRST OF ALL

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

<cfset date1 = #DateFormat(FORM.StartDate,"mm/dd/yyyy")#>

This logic is setting the dates as a text representation that are readable to humans, but are actually a very poor format for computers.

SECOND OF ALL

One does not usually do <cfset....> like that inside the <cfquery...> but it is not illeagal.

THRID OF ALL

Using the CFML dateCompare() function is very UNLIKELY to filter your data as you desire, but I can not say for sure based on the logic you have shown.

I would expect your database <cfquery...> block should like something like this:

<cfquery....>
   SELECT aField, bField, cField
   FROM aTable
   WHERE aDateField BETWEEN <cfqueryparam value="#form.startDate#" cfsqltype="cf_sql_date"> AND <cfqueryparam value="#form.endDate#" cfsqltype="cf_sql_date">
</cfquery>

This logic does assume that the date field(s) in the database are of a "DATE" data type, NOT text fields that hold strings that look like dates to humans.

[Guest]

Hi ilssac,

Thanks for your feedback.

I have tried using the query provided:

<cfquery....>
   SELECT aField, bField, cField
   FROM aTable
   WHERE aDateField BETWEEN <cfqueryparam value="#form.startDate#" cfsqltype="cf_sql_date"> AND <cfqueryparam value="#form.endDate#" cfsqltype="cf_sql_date">
</cfquery>

Its giving me an error message saying

FORM.StartDate is an invalid date or time string..

On the line pointing to the where clause.

Thanks Again.

Vijayvijay77.

[Guest]

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.

[ilssac · Accepted solution]

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.

[ilssac]

vijayvijay77 wrote:

Its giving me an error message saying

FORM.StartDate is an invalid date or time string..

On the line pointing to the where clause.

Then that is a problem.  It is up to you to know what data you are working with.  WE are not looking at your system.  WE can NOT see what data is being input, what data is in the database, how the database schema is put together. Etc.

We can only make suggestions based on our experience and you have to take that advice an apply it to your situation.

If you need more help then that, then you are approaching the relm of signing contracts and having others do the work.

My usual rate for after hours, telecomute work is $50 U.S. an hour.

[Guest]

Thanks SIR, for all the valuable time and feedback you have provided. I really appreciate your help.

[Owainnorth]

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.

[Dan_Bracuk]

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.

[Reed_Powell-ttnmOb]

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

[Guest]

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.

[ilssac]

vijayvijay77 wrote:

Am I making a mistake in the where clause?

If the code is NOT throwing any exceptions AND it IS returning only the desired records, then it should be a fine WHERE clause.  Only YOU can answer those questions.

[Guest]

Hi ilssac,

Thank You Sir, Its working now, the query you provided with <cfqueryparam> tags is working.I used the same query previously when provided by you, it was not working then but it is working now. I dont know why?

Thanks again,

Vijayvijay77.

[ilssac]

vijayvijay77 wrote:

I dont know why?

Different data in the form fields would be the first suspicion.

You will REALLY want to validate the form data so your application behaves nicely when users input bad data.

[Dan_Bracuk]

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

[Guest]

Hi,

Its now working with the query I posted in the above comment.

I have used that query when provided by the user "ILLASC", but was not working then, its working now.

Thanks for the inputs I received and I really appreciate everyones suggestions and feedback. I really feel like home, when I post a question on these forum.

I really appreciate it from the bottom of my heart.

Thanks Again,

Vijayvijay77.