Searching Dates

[Supercyberal]

Hi Guys

I'm having a problem retriving dates when i use this query:
<cfif Form.TxtDate GT "">
<cfif Form.FindDate EQ "EQUALS">
<cfset WhereClause = WhereClause & " and O_Date = " &
CreateODBCDate(form.TxtDate)>
<cfelseif Form.FindDate EQ "AFTER">
<cfset WhereClause = WhereClause & " and O_Date > " &
CreateODBCDate(form.TxtDate)>
<cfelseif Form.FindDate EQ "BEFORE">
<cfset WhereClause = WhereClause & " and O_Date < " &
CreateODBCDate(form.TxtDate)>
</cfif>
</cfif>

When a type a date, it doesnt bring nothing.

Thank you and waiting for further answers

[]'s
Alvaro Neto
[email]alvaro_galdino@msn.com[/email]
[email]alvaro_galdino@earthlink.net[/email]

[efecto747]

You'd be better of using the DATEDIFF() function (assuming your database has
one which it should).

You code would look something like this:



<cfif Form.FindDate EQ "EQUALS">
<cfset WhereClause = WhereClause & " and DATEDIFF(d,O_Date," &
CreateODBCDate(form.TxtDate) & " = 0">
<cfelseif Form.FindDate EQ "AFTER">
<cfset WhereClause = WhereClause & " and DATEDIFF(d,O_Date," &
CreateODBCDate(form.TxtDate) & " < 0">
<cfelseif Form.FindDate EQ "BEFORE">
<cfset WhereClause = WhereClause & " and DATEDIFF(d,O_Date," &
CreateODBCDate(form.TxtDate) & " > 0">
</cfif>

[The ScareCrow]

I don't think you should be using the follwoing cfif, not really what you want
to do
<cfif Form.TxtDate GT "">

Try
<cfif Len(Trim(Form.TxtDate)>
Or
<cfif Form.TxtDate NEQ "">

Have you dumped the complete query string to ensure it is being constructed
correctly ?
Does the O_Date column only contain dates or dates and Times ?

Ken