CURRENT_DATE vs CURRENT_WEEK

ACDC

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

ACDC,

What problem are you having? I'm not sure what your question is.

Tom

ACDC

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

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

Tom

I tried your example but it does not work

ACDC

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

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

no that one does not work, it returns zero records

tford

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

It turns out this query works on Derby, but not on MySql:

FIND Invoice WHERE WEEK(Invoice.InvDate)=CURRENT_WEEK

Tom

rocketman

Try

FIND Appointment WHERE Appointment.Type='Meeting' AND WEEK(Appointment.ActivityDate)=CURRENT_WEEK AND YEAR(Appointment.ActivityDate)=CURRENT_YEAR

pbrad

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

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.