Re: Date list using SQL IN operator

[Keith Levenson]

Ok, I figured out I should use ListQualify on the list to add the single quotes and preservesinglequotes within the query to preserve the quotes for revised code of:

<cfoutput query="checkForEmp21Overlap">
<cfset overlapRecordCount=
ListAppend(overlapRecordCount,DateFormat(myStart,"mm/dd/yyyy"))>
<cfif DateFormat(myStart,"mm/dd/yyyy") gte DateFormat(finalDay,"mm/dd/yyyy")>
<cfbreak>
</cfif>
</cfoutput>
<cfset overlapRecordCount = ListQualify(overlapRecordCount,"'",",","CHAR")>

<cfquery name="megaquery_output_recordspecific_dateRestricted" dbtype="query">
SELECT *
FROM megaquery_output_recordspecific
WHERE (myStart BETWEEN
<cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#formattedBeginDay#"> and
<cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#formattedEndDay#">)
<cfif listlen(overlapRecordCount) gt 0>
AND (myStart NOT IN (#preserveSingleQuotes(overlapRecordCount)#))
</cfif>
ORDER BY myStart;
</cfquery>

Now the problem is I get a "Query Of Queries runtime error.
Unsupported type comparison."

I tried using cfqueryparam to set the type to "date", but it doesn't seem to be compatible with lists.

Any new suggestions?

Thanks!

---

[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]

---