asked on

Filter dates

I want to list calendar events beginning with the current month forward. With my code below, I'm still getting the entire calendar event list from the day it was created.

SQL 2000, caleventdate is datetime.

<cfset setMonth = month(now()) & "/01/" & year(now())>

<cfquery name="qlistDates" datasource="#DSN#">
select caleventdate, caleventtitle
from calevents
where caleventdate >= <cfoutput>#setMonth#</cfoutput>
order by caleventdate
</cfquery>

<cfoutput query="qlistDates">
#dateformat(caleventdate, 'mm/dd/yyyy')# - #caleventtitle#<br />
</cfoutput>

ASKER CERTIFIED SOLUTION

usachrisk1983

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kbtudor

ASKER

It works on my local machine, but not the remote server. The error message is Variable SETMONTH is undefined.

Local machine is SQL Express 2005 - remote server is SQL Server 2000

Thanks

usachrisk1983

Ok, do this against your SQL Server 2000 database using CF:

<cfset setMonth = month(now()) & "/01/" & year(now())>
<cfoutput>setMonth = #setMonth#</cfoutput>

Does setMonth resolove correctly? Are you sure you have the query exactly as I put it above?

Thanks!

kbtudor

ASKER

It's has something to do with session. If I copy your code onto a blank page - it works great. If I copy it onto the page I was working on, I keep getting errors. It's defininately on my end.

Thanks for the speedy response.

Can you help me understand why the CreateODBCDate worked and the setMonth did not? That's what I had originally.

usachrisk1983

Thanks for the points :)

CreateODBCDate formats the string like the DB wants to see it. If you're sending just the variable, it may not be read correctly. When working with dates, always use another function to format it correctly. You should remember to look into CFQUERYPARAM for things like this, more info here:

http://www.coldfusionusers.com/cfblog/2006/05/18/the-importance-of-cfqueryparam/