Retrieving records by date

Question

I've been having a tough time with retrieving records before
a certain date.

I had this code working on a site in the past and for some
reason, it's not working anymore - don't know if it's because of a
ColdFusion upgrade or if I missed something.

I am trying to bring up only records that have a date code
from earlier than now (so we can set up some records to appear on
the site later...)

I am using:

SELECT PostID, Title, Post, ImageBox, DatePosted, Active
FROM TheRecords
WHERE (DatePosted < #Now()#) AND (Active = 'Yes')
ORDER BY DatePosted DESC

Though it doesn't give an error, it seems to retrieve all of
the records, even those that are scheduled for later.

Any ideas?

Thanks!

Luc

cf_dev2 · Accepted Answer

If "DatePosted" is not a date type column, that may be the source of your problem. Otherwise, cfdump the query and check the results. Are the "DatePosted" values less than "CurrentTime" ? If they are, it suggests a problem with the values in the "DatePosted" column, rather than your where clause.

SELECT PostID, Title, Post, ImageBox, DatePosted, Active, #Now()# AS CurrentTime
FROM TheRecords
WHERE DatePosted < #Now()# AND Active = 'Yes'
ORDER BY DatePosted DESC

lther · Answer

Thanks a lot for the reply. That seems to be exactly what itwas - it was set as varchar.jdeline, thanks a lot for your help too!Luc

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded