Skip to main content
February 28, 2010
Answered

Problem with select query that includes a date

  • February 28, 2010
  • 2 replies
  • 526 views

I've been fighting with this for about 4 hours - yes, quite literally... I've never been good with date stuff. I have a brain block.

Anyway, here's the situation:

I have an Access database that has a time/date field. There are no times, just a date in mm/dd/yyyy format.

That table is a list of classes held for the past three years. I want to make a query that only shows the classes that have ended after January 1 of this year. I don't want to put 01/01/2010 in the query itself because I want it to still work next year.

This is the current query:

<cfquery name="classes" datasource="#DSN#" dbtype="ODBC">

SELECT classesTitles.ClassTitle, classesTitles.ClassDesc, classCatagories.catagoryName, classes.classStart, classes.classEnd, classes.classNotes, owners.fName, owners.lName, classTime.timeSlot, classes.classID, classes.classLimit

FROM classCatagories INNER JOIN (((classes INNER JOIN classesTitles ON classes.classTitle = classesTitles.classTitleID) INNER JOIN classTime ON classes.classTime = classTime.timeID) INNER JOIN owners ON classes.classInstructor = owners.ownerID) ON classCatagories.catagoryID = classesTitles.ClassType

WHERE  classes.classEnd <  01/01/#DatePart("yyyy", now())#

ORDER BY classes.classStart, classCatagories.catagoryName, classTime.timeSlot, classesTitles.ClassTitle

</cfquery>

I don't get any results, but I should get a list of every class that has an end date before January 1 of this year - about 200 records.

When I swap around the where clause to:

<cfquery name="classes" datasource="#DSN#" dbtype="ODBC">

SELECT classesTitles.ClassTitle, classesTitles.ClassDesc, classCatagories.catagoryName, classes.classStart, classes.classEnd, classes.classNotes, owners.fName, owners.lName, classTime.timeSlot, classes.classID, classes.classLimit

FROM classCatagories INNER JOIN (((classes INNER JOIN classesTitles ON classes.classTitle = classesTitles.classTitleID) INNER JOIN classTime ON classes.classTime = classTime.timeID) INNER JOIN owners ON classes.classInstructor = owners.ownerID) ON classCatagories.catagoryID = classesTitles.ClassType

WHERE  classes.classEnd > 01/01/#DatePart("yyyy", now())#

ORDER BY classes.classStart, classCatagories.catagoryName, classTime.timeSlot, classesTitles.ClassTitle

</cfquery>

I get EVERY class in the database, and I should only be getting about 50

The classes are about 8 weeks long, the begin date and end date are in the database as date/time fields, so I need to show everything that ended in 2010, regardless of when it started.

I'm sure it's something glaringly obvious, but it's escaping me.

THANKS

Michelle

    This topic has been closed for replies.
    Correct answer Dan_Bracuk

    The format of your date field in access is irrelevent to the situation at hand.  Use proper date objects instead of trying to build a suitable string.  For the first of January of the current year, the coldfusion functions createdate(), year(), and now() are your freinds.  Then, in case Adam's post was not clear, use cfqueryparam with the resulting variable.

    2 replies

    Dan_BracukCorrect answer
    Inspiring
    February 28, 2010

    The format of your date field in access is irrelevent to the situation at hand.  Use proper date objects instead of trying to build a suitable string.  For the first of January of the current year, the coldfusion functions createdate(), year(), and now() are your freinds.  Then, in case Adam's post was not clear, use cfqueryparam with the resulting variable.

    February 28, 2010

    That did it!

    THANK  YOU!

    Inspiring
    February 28, 2010

    I can't vouch for the rest of the SQL, but pass your date as a parameter, rather than hard-coding it into your SQL string.

    --

    Adam