Re: Date list using SQL IN operator

2004-04-09 Thread 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/))			
cfif DateFormat(myStart,mm/dd/) gte DateFormat(finalDay,mm/dd/)
	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]

Date list using SQL IN operator

2004-04-08 Thread Levenson, Keith 
I have a query that creates a list of dates

cfoutput query=checkForOverlap
	cfset overlapRecordCount=
	
ListAppend(overlapRecordCount,DateFormat(myStart,mm/dd/))

		cfif DateFormat(myStart,mm/dd/) gte
DateFormat(finalDay,mm/dd/)
			cfbreak
		/cfif	
/cfoutput

I want to use this list in an SQL query:

cfquery name=other_query dbtype=query
	SELECT * 
	FROM main_query
	WHERE (myStart BETWEEN
	cfqueryparam cfsqltype=CF_SQL_TIMESTAMP
value=#formattedBeginDay# and
	cfqueryparam cfsqltype=CF_SQL_TIMESTAMP
value=#formattedEndDay#)
	AND myStart NOT IN (#overlapRecordCount#)
	ORDER BY myStart
/cfquery

The problem is obvious in that the single quotes are not wrapped around each
element in the list when the IN is performed.Are there any CF string
functions I can try to wrap the dates in the list with single quotes?

Many thanks!
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]