Compare dates in a query

<CFSET date1 = DateFormat(Now()-22,"mm/dd")>
This is not act method in evaluating a time diff

also your SQL
output_login.SentDt > #date1#  
also has issues.
 
First you need to set your vars time now .. and the timediffrence .

<cfset ndate = CreateODBCDateTime(now()) >

<cfset nday  = CreateTimeSpan(21, 0, 0, 0)>
<cfset newdate = (ndate + nday  )>


<cfif now() gt dateformat(newdate,"dd/mm/yy") >


</cfif>
         

You now have the nessisary vars to use .. both today and 21 days from now -

Sorry just forgot to put the field name in the where clause

<CFSET Contactid = ListFirst(GetAuthUser())>
<cfset date2=dateformat(now(),"dd/mm/yyyy")
<CFSET date1 = DateFormat(Now()-22,"dd/mm/yyyy")>
<cfquery name="dr" datasource="DR" >
SELECT *
FROM output_login
WHERE output_login.ContactID = #Contactid#
AND output_login.sentDt between #date1# and #date2#
ORDER BY SentDt, OriginalSource, ManagerTeamVendor, SRNum
</cfquery>

sandeep its at least working, doing something.

however, the dates of the rows are april 4th and now with your code they are not showing up. I even set the query up for a 40 day differential between today and the end of march, and the rows still do not show...ideas?

sandeep my database format is m/d/yyyy. i tried that too in the code and same problem, nothing is now showing up in the web table.  i am using ms access

can u put me the debugged query here and also the date data stored in database.(Basically as a date or as datetime)

If that now -22 does not work we can work like this

<cfset date1 = dateadd("d",-21,now())>
<cfset date1 = dateformat(date1,"m/d/yyyy")>
<cfset date2 = dateformat(now(),"M/D/YYYY")>

And write the same query.

Try this and let me know.

REgards,
Sandeep

there is not debugged query because there is no error.

This still does not work and i even tried making the query easier by just doing this

output_login.SentDt > #date1#  for the date condition

i am thinking this is a problem with Sent.Dt because of the previous comment by that other guy above, he said my SQL was not cool

i dont think so. I just suspect the date stored in the database.Do u know how to debug ur query. Thereis one option in cf admin u put ur IP address and then u can see all the queries and etc n then u can paste that query in access and try to execute n find out whats wrong.

try that or else do this if its difficult to do
<cfquery debug="yes" database="..>
</cfquery>

cfquery has a attribute to debug a query just do that and try to cpy it n send it here. I will look at it.

Regards
Sandeep

anand, hello. it didnt work. heres the error

Error Executing Database Query.  
[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.  
 
The Error Occurred in C:\CFusionMX\wwwroot\DRWEB\TMPws1xe85xe.cfm: line 162
 
160 : AND output_login.SentDt > #CreateODBCDate(dateadd("d",-40,now()))#
161 : ORDER BY SentsDt, OriginalSource, ManagerTeamVendor, SRNum
162 : </cfquery>
163 : <CFSET Team = ListRest(GetAuthUser())>
164 : <cfquery name="Disputes" datasource="DR">

 

--------------------------------------------------------------------------------
 
SQL    SELECT * FROM output_login WHERE output_login.ContactID = 1 AND output_login.SentDt > {d '2003-03-22'} ORDER BY SentsDt, OriginalSource, ManagerTeamVendor, SRNum  
DATASOURCE   DR
VENDORERRORCODE   -3010
SQLSTATE   07002