Using cfif in a where clause

Question

Hi all,This question is sort of related to a previous post of mine about querying dates. I'm trying to use a cfif statement in my where clause to determine if a field is empty or not. Here's the code: SELECT * FROM SITE:Calendar WHERE DatePart('yyyy', [StartDate]) = and StartDateEndDate < and Archive = ORDER BY StartDate ASC, StartTime ASCMy issue that that CF tells me EndDate is not defined. EndDate is a field in the table SITE:Calendar. There has to be a way to make this work, no? Thanks!

Steve Sommers · Accepted Answer

You are mixing ColdFusion variables (which cfif requires) and SQL column names. Think of it this way, all CF tags and function execute before the SQL query is sent to the SQL server. You're mixing this and trying to run the CF statement on the SQL server and thus the failure.

You need something like this:

SELECT *

FROM SITE:Calendar

WHERE DatePart('yyyy', [StartDate]) = <cfqueryparam value="#Dateformat(Today, 'yyyy')#" cfsqltype="CF_SQL_DATE" />

and (

(enddate is not null and EndDate < <cfqueryparam value="#Today#" cfsqltype="CF_SQL_DATE" />)

or (enddate is null and StartDate < <cfqueryparam value="#Today#" cfsqltype="CF_SQL_DATE" />)

)

and Archive = <cfqueryparam value="0" cfsqltype="CF_SQL_INTEGER" />

ORDER BY StartDate ASC, StartTime ASC

</cfquery>

Also, I think your cfif logic was backwards. Instead of NEQ I think you meant EQ because having an EndDate EQ '' and then comparing EndDate to Today (else clause) doesn't make sense.

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