<cfquery name="lQuery2" dbtype="query">
SELECT UserName, SearchString, Count(SearchString) AS Occurences
FROM qTmp
WHERE (#Now()# - DateTime) < 7
GROUP BY UserName, SearchString
</cfquery>
>This is what I'm doing, and I get an error. I'm using SQL Server 2000
>
><cfquery name="lQuery2" dbtype="query">
>SELECT UserName, SearchString, Count(SearchString) AS Occurences
>FROM
>WHERE (#DateFormat(Now(),"yyyy/mm/dd")# #TimeFormat(Now(),"HH:mm:ss")#
>- #qTmp.DateTime#) < 7
>qTmp
>GROUP BY UserName, SearchString
></cfquery>
>
>I get this error:
>
>Query Of Queries syntax error.
>Encountered "WHERE" at line 0, column 0.
>
>>ColdFusion Programmer wrote:
>>> Hi Jochem, Can you explain what the where statement in your code
>>> does, I don't understand the the "EXTRACT(DAYS FROM
>>> CURRENT_TIMESTAMP - DateTime) < 7" bit
>>
>>Read it inside out:
>>
>>CURRENT_TIMESTAMP -> the current date and time, 'now'
>>DateTime -> your field with the moment of the search, 'then'
>>CURRENT_TIMESTAMP - DateTime -> the interval between now and
>then
>>EXTRACT(DAYS FROM ..) -> the number of whole days in that
>interval
>>
>>So what it says is that DateTime should be less than 7 days in the
>>past in standard SQL syntax. If your database does not support
>>standard SQL syntax, you might have to rewrite it in something your
>>database understands, but that should be explained in the manual.
>>
>>Jochem
>>
>>
>>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
