CURRENT_DATE vs CURRENT_WEEK

ACDC · 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 · 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 · 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 · 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 · Post by **ACDC** » Sun Mar 02, 2008 9:13 pm

Tom

I tried your example but it does not work

ACDC

ACDC · 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 · 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 · Post by **ACDC** » Sun Mar 02, 2008 9:30 pm

no that one does not work, it returns zero records

tford · 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 · 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 · 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 · 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 · 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.