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:



	ListAppend(overlapRecordCount,DateFormat(myStart,"mm/dd/"))>			

	
	
			
 


		SELECT * 
		FROM megaquery_output_recordspecific
		WHERE (myStart BETWEEN
		 and
		)
		
		AND (myStart NOT IN (#preserveSingleQuotes(overlapRecordCount)#))
		
		ORDER BY myStart;
	

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

[Levenson, Keith]

I have a query that creates a list of dates


	
	
ListAppend(overlapRecordCount,DateFormat(myStart,"mm/dd/"))>

		
DateFormat(finalDay,"mm/dd/")>
			
			


I want to use this list in an SQL query:


	SELECT * 
	FROM main_query
	WHERE (myStart BETWEEN
	
value="#formattedBeginDay#"> and
	
value="#formattedEndDay#">)
	AND myStart NOT IN (#overlapRecordCount#)
	ORDER BY myStart


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]