Avatar of kbtudor
kbtudor
Flag for United States of America 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>
Web Servers

Avatar of undefined
Last Comment
usachrisk1983

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
usachrisk1983

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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/