If you have questions or if you want to share your opinion about Aware IM post your message on this forum
ACDC
Posts: 1143 Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA
Post
by ACDC » Sun Mar 02, 2008 1:30 pm
I was hoping to get CURRENT_WEEK working like CURRENT_DATE but it looks like the CURRENT_WEEK function needs some other bits added to the query to get it working
Does anyone have any ideas ?
Thank You
tford
Posts: 4238 Joined: Sat Mar 10, 2007 6:44 pm
Post
by tford » Sun Mar 02, 2008 3:00 pm
ACDC,
What problem are you having? I'm not sure what your question is.
Tom
ACDC
Posts: 1143 Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA
Post
by ACDC » Sun Mar 02, 2008 8:56 pm
Hi Tom
This query works:
FIND Appointment WHERE Appointment.Type='Meeting' AND Appointment.ActivityDate=CURRENT_DATE
but this one does not
FIND Appointment WHERE Appointment.Type='Meeting' AND Appointment.ActivityDate=CURRENT_WEEK
tford
Posts: 4238 Joined: Sat Mar 10, 2007 6:44 pm
Post
by tford » Sun Mar 02, 2008 9:01 pm
ACDC,
The issue is that you are comparing a date attribute to a week number. You have to convert the date attribute to a week number.
Try this:
FIND Appointment WHERE Appointment.Type='Meeting' AND WEEK(Appointment.ActivityDate)=CURRENT_WEEK
Tom
ACDC
Posts: 1143 Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA
Post
by ACDC » Sun Mar 02, 2008 9:13 pm
Tom
I tried your example but it does not work
ACDC
ACDC
Posts: 1143 Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA
Post
by ACDC » Sun Mar 02, 2008 9:21 pm
Tom
I also tried creating an ActivityWeek Attribute and managed to get this updating with a rule ie ActivityWeek=CURRENT_WEEK. The week updates correctly, but when I insert CURRENT_WEEK in the query, it returns weeks from previous years also. So I need to find away to suppress prior years
ACDC
tford
Posts: 4238 Joined: Sat Mar 10, 2007 6:44 pm
Post
by tford » Sun Mar 02, 2008 9:26 pm
So, does this work except it find stuff from multiple years?
FIND Appointment WHERE Appointment.Type='Meeting' AND WEEK(Appointment.ActivityDate)=CURRENT_WEEK
Tom
ACDC
Posts: 1143 Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA
Post
by ACDC » Sun Mar 02, 2008 9:30 pm
no that one does not work, it returns zero records
tford
Posts: 4238 Joined: Sat Mar 10, 2007 6:44 pm
Post
by tford » Sun Mar 02, 2008 9:37 pm
ACDC,
I got it to work in a test bsv. If you'd like to see it, send me a PM with your email address.
Tom
tford
Posts: 4238 Joined: Sat Mar 10, 2007 6:44 pm
Post
by tford » Fri Jan 23, 2009 10:00 pm
It turns out this query works on Derby, but not on MySql:
FIND Invoice WHERE WEEK(Invoice.InvDate)=CURRENT_WEEK
Tom
rocketman
Posts: 1252 Joined: Fri Jan 02, 2009 11:22 pm
Location: Preston UK
Contact:
Post
by rocketman » Wed Jan 28, 2009 1:30 pm
Try
FIND Appointment WHERE Appointment.Type='Meeting' AND WEEK(Appointment.ActivityDate)=CURRENT_WEEK AND YEAR(Appointment.ActivityDate)=CURRENT_YEAR
pbrad
Posts: 781 Joined: Mon Jul 17, 2006 11:03 pm
Location: Ontario, Canada
Post
by pbrad » Wed Jan 28, 2009 4:57 pm
This could be a red herring but I had the same issue and could only get it to show items from what I considered to be the current week using CURRENT_WEEK-1. The query seemed to treat next week as the current week.
Pete
aware_support
Posts: 7525 Joined: Sun Apr 24, 2005 12:36 am
Contact:
Post
by aware_support » Thu Jan 29, 2009 7:00 am
Aware IM calculates week for MySQL 5.0 as WEEK(date). In MySQL the return of this function is controlled by the system variable called default_week_format (refer to MySQL documentation). Make sure that you set the value of this variable appropriately.
Aware IM Support Team