Dick Applebaum wrote:
On Apr 4, 2004, at 8:21 AM, Jochem van Dieten wrote:
http://www.mysql.com/doc/en/Date_and_time_functions.html
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,
Jochem
Do you have a reference?
All I can find is:
ª Added WEEK and QUARTER values as INTERVAL arguments for DATE_ADD()
and DATE_SUB() functions.
at:
http://www.mysql.com/doc/en/News-5.0.0.html
Anyway, MySQL 5 is alpha, so it won't be available for a while
Dick
On Apr 5, 2004, at 8:07
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.
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
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:
SELECTdatefld
FROMtable
WHERE EXTRACT(WEEK FROM datefld) = EXTRACT(WEEK FROM CURRENT_DATE)
AND
EXTRACT(YEAR FROM
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:
SELECTdatefld
FROMtable
WHERE EXTRACT
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#
SELECTdate as datefld
Date is a reserved word. Change it while you
-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
JT said:
cfquery name=weekly datasource=#application.ds#
SELECTtestdate
FROMcompletedtest
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 :
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/
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
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
JT wrote:
This did it.
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
12 matches
Mail list logo
