...tony
tony weeg
senior web applications architect
navtrak, inc.
www.navtrak.net
[EMAIL PROTECTED]
410.548.2337
-----Original Message-----
From: ColdFusion Programmer [mailto:[EMAIL PROTECTED]
Sent: Friday, October 10, 2003 8:35 AM
To: CF-Talk
Subject: Re:SQL Statement help!
Sorry this is the query I'm using
<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]
