<cfset CURRENT_DATE = #now()#>
<cfset today = #now()#>
<cfset week = #week(today)#>
<cfset type = "ww">
<!--- <cfset lastweek = --->
<cfquery name="weekly" datasource="#application.ds#">
select * from Skills.completedtest
where week(date) = #week#-1
order by date,time
</cfquery>
But I am confused . why doesnt <cfset week = #week(today)#> set this week to
this weeks number?
I had to use where week(date) = #week# -1
to get correct records
-----Original Message-----
From: Dick Applebaum [mailto:[EMAIL PROTECTED]
Sent: Sunday, April 04, 2004 9:10 AM
To: CF-Talk
Subject: Re: query for all records in this week
Oops, forgot the best part---
you can use
WHERE WEEK(BuyDate) ....
But be careful ---
WEEK("1998-01-01") AS WK, gives 0
WEEK("2000-12-31") AS WK1, gives 53
If you have an interactive db client, you might want to experiment.
HTH
Dick
On Apr 4, 2004, at 9:00 AM, Dick Applebaum wrote:
> I just happen to be playing with some MySQL dbs with an interactive
> db
> client ---
>
> I tried the syntax
>
> >
> > EXTRACT(WEEK FROM testdate)
>
> and get the same error, while:
>
> EXTRACT(DAY FROM testdate)
>
> works fine
>
> Checking the MySQL docs at:
>
> http://webdocs.math.univ-rennes1.fr/MySQL/mysql-3.23.52/
> manual_Date_and_time_functions.html
>
> about 1/3 way down the page, shows the types supported for EXTRACT
>
> Week is not mentioned.
>
> However, MySQL does support
>
> mysql> select WEEK('1998-02-20');
> -> 7
>
> So that is likely the cause of confusion;
>
> HTH
>
> Dick
>
> On Apr 4, 2004, at 8:21 AM, Jochem van Dieten wrote:
>
> > JT said:
> > >
> > > <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
> >
> > I think you would have to take this up with MySQL, since it should
> > work according to the manual:
> > http://www.mysql.com/doc/en/Date_and_time_functions.html
> >
> > Jochem
> >
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
