new code is
<cfset CURRENT_DATE = #now()#>
<cfset today = #now()#>
<cfset week = #week(today)#>
<cfquery name="weekly" datasource="#application.ds#">
SELECT testdate
FROM completedtest
WHERE EXTRACT(WEEK FROM testdate) = EXTRACT(WEEK FROM CURRENT_DATE)
AND
EXTRACT(YEAR FROM testdate) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY EXTRACT(DAY FROM testdate)
</cfquery>
The ERROR is :
Error Executing Database Query.
Syntax error or access violation: You have an error in your SQL syntax
near 'WEEK FROM testdate) = EXTRACT(WEEK FROM CURRENT_DATE) AND EX' at line
3
ANY IDEAs
-----Original Message-----
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Sunday, April 04, 2004 7:59 AM
To: CF-Talk
Subject: RE: query for all records in this week
JT said:
> I get this error
>
> Syntax error or access violation: You have an error in your SQL
> syntax near 'WEEK FROM datefld) = EXTRACT(WEEK FROM CURRENT_DATE)
> AND EXT' at line 3
> <cfquery name="weekly" datasource="#application.ds#">
> SELECT date as datefld
Date is a reserved word. Change it while you still can.
> FROM completedtest
> WHERE EXTRACT(WEEK FROM datefld) = EXTRACT(WEEK FROM
> CURRENT_DATE)
I don't think you can use a column alias in the WHERE.
> AND
> EXTRACT(YEAR FROM datefld) = EXTRACT(YEAR FROM
> CURRENT_DATE)
> GROUP BY EXTRACT(DAY FROM datefld)
> </cfquery>
Jochem
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
