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
this is the code
<cfset today = #now()#>
<cfset week = #week(today)#>
<cfquery name="weekly" datasource="#application.ds#">
SELECT date as datefld
FROM completedtest
WHERE EXTRACT(WEEK FROM datefld) = EXTRACT(WEEK FROM CURRENT_DATE)
AND
EXTRACT(YEAR FROM datefld) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY EXTRACT(DAY FROM datefld)
</cfquery>
-----Original Message-----
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Sunday, April 04, 2004 7:27 AM
To: CF-Talk
Subject: Re: query for all records in this week
JT said:
> I have a MySQL database table that has a date column , how can I
> query for all records in this week and group on day of week.
Define "this week" :-)
This week of this year:
SELECT datefld
FROM table
WHERE EXTRACT(WEEK FROM datefld) = EXTRACT(WEEK FROM CURRENT_DATE)
AND
EXTRACT(YEAR FROM datefld) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY EXTRACT(DAY FROM datefld)
This week of each year:
SELECT datefld
FROM table
WHERE EXTRACT(WEEK FROM datefld) = EXTRACT(WEEK FROM CURRENT_DATE)
GROUP BY EXTRACT(DAY FROM datefld)
The last 7 days:
SELECT datefld
FROM table
WHERE datefld BETWEEN CURRENT_DATE AND CURRENT_DATE - INTERVAL 7 DAY
GROUP BY EXTRACT(DAY FROM datefld)
Be carefull though, a week doesn't start on the same day everywhere.
Jochem
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
