Date comparison failure

[Stormpool]

Does this qualify as an advanced technique? Sorry if not. This is a section of
stock control I am doing to track supplier orders. I have a two text input
boxes, one has a value of #dateformat(NOW(), 'dd/MM/yyyy')# and the other is
blank. When i process the form, a query puts these dates into a table with the
following code. UPDATE tbl_supplierorders SET suporder_orderdate =
#createodbcdate(dateformat(form.suporder_orderdate , 'dd/MM/yyyy'))#,
suporder_duedate = #createodbcdate(dateformat(form.suporder_duedate,
'dd/MM/yyyy'))# WHERE suporder_refID = <cfif URL.action EQ
'view'>'#URL.suporder_refid#'<cfelse>'#session.sup ref_id#'</cfif> The fields
in the table then list the dates in an english format (without the bracketed
date, just showing you what it represents) 30/02/2005 (30th Feb) 01/12/2004
(1st Dec) 03/04/2004 (3rd April) I list these on another page and compare
them to todays date with the following code. <cfoutput query='Shbxordersdue'>
<cfif #dateformat(suporder_duedate, 'dd/MM/yyyy')# EQ #dateformat(NOW(),
'dd/MM/yyyy')#><tr bgcolor='##FFCCFF'><cfelse><tr></cfif> <td><a
href='admin_supplier_orders.cfm?supplier=#supplier _ID#&amp;action=view&amp;supor
der_refid=#suporder_refID#'>#supplier_name#</a> - <cfif
#dateformat(suporder_duedate, 'dd/MM/yyyy')# EQ #dateformat(NOW(),
'dd/MM/yyyy')#> <span class='style3'>Due: Today</span> <cfelseif
#dateformat(suporder_duedate, 'dd/MM/yyyy')# GT #dateformat(NOW(),
'dd/MM/yyyy')#> Due: #dateformat(suporder_duedate, 'dd/MM/yyyy')#
<cfelseif #dateformat(suporder_duedate, 'dd/MM/yyyy')# LT #dateformat(NOW(),
'dd/MM/yyyy')#> <span class='style2'>Over Due: #dateformat(suporder_duedate,
'dd/MM/yyyy')#</span> </cfif> </td> </tr> </cfoutput> But
the output is something like this.... Shopboxuk Orders Outstanding Eupa
Trading Ltd - Due: 04/04/2005 Eupa Trading Ltd - Over Due: 03/04/2005 Eupa
Trading Ltd - Due: Today Eupa Trading Ltd - Due: Today Eupa Trading Ltd -
Due: Today Eupa Trading Ltd - Over Due: 30/03/2005 Eupa Trading Ltd - Over
Due: 30/03/2005 Eupa Trading Ltd - Over Due: 30/03/2005 Eupa Trading Ltd -
Over Due: 30/03/2005 Eupa Trading Ltd - Over Due: 29/03/2005 It has
shown orders due today correctly, and that order at the top due on the 4th
April...but the date of 03/04/2005 is shown as overdue, incorrectly. It
manages to list it correclty in the order of dates... and puts it in the right
place. But why has it done the calculation wrong, and i can only assume it has
read it as 4th March. Thanks, Dan

[Stormpool]

Credit to Falconseye on [url]www.easycfm.com[/url] forums. use DateCompare(date1, date2)
returns - 1 if date1 < date2 0 if date1 = date2 and
1 if date1 > date2 My code with this implemented.. <cfoutput
query='Shbxordersdue'> <cfset nowdate = dateformat(NOW(),
'dd/MM/yyyy')> <cfset date = datecompare(suporder_duedate, nowdate)>
<cfif date EQ 0><tr bgcolor='##FFCCFF'><cfelse><tr></cfif>
<td><a
href='admin_supplier_orders.cfm?supplier=#supplier _ID#&amp;action=view&amp;supor
der_refid=#suporder_refID#'>#supplier_name#</a> - <cfif date EQ 0>
<span class='style3'>Due: Today</span>
<cfelseif date EQ 1> Due:
#dateformat(suporder_duedate, 'dd/MM/yyyy')#
<cfelseif date EQ -1> <span class='style2'>Over Due:
#dateformat(suporder_duedate, 'dd/MM/yyyy')#</span> </cfif>
</td> </tr> </cfoutput>

[Steve Sommers]

If suporder_duedate is a date field, you can just compare the dates directly:

<cfif suporder_duedate EQ Int((NOW())>...
<cfelseif suporder_duedate GT Int((NOW())>...

The problem you are having is that you are currently comparing string values
in the format of dd/mm/yyyy. If you don't trust the above code, you will need
to change your format to yyyy/mm/dd for the comparisons.

Also, to avoid the 1 in a billion chance that Now() increments between the
multiple comparisons (in which case it will confuse the crap out of you and
will be difficult, if not impossible to debug), I set a local variable to Now()
or Int(Now()) in your case and use this temp variable in all the comparisons so
that it guarantees that all the comparisons use the same value:

<cfset currentdt=Int(Now())>
<cfif suporder_duedate EQ currentdt>...
<cfelseif suporder_duedate GT currentdt>...

[Stormpool]

My code failed today on 01/04/2005 as it thought it should compare it to 4th
Jan :o/ But with your advice i rewrote a bit of the code <cfset
nowdate = dateformat(NOW(), 'yyyy-MM-dd')> <cfset duedate =
dateformat(suporder_duedate, 'yyyy-MM-dd')> <cfset date =
datecompare(duedate, nowdate)> And now it's ok, tried the server on numerous
dates and all works fine.