Filtering for CURRENT_WEEK in queries

Contains tips for configurators working with Aware IM
Post Reply
pbrad
Posts: 781
Joined: Mon Jul 17, 2006 11:03 pm
Location: Ontario, Canada

Filtering for CURRENT_WEEK in queries

Post by pbrad »

Hi,

I understand in queries that if I want to filter for a record attribute = Current_Date that I have to use the syntax:

FIND Client WHERE Client.CreationDate=DATE_ADD(CURRENT_DATE,0)

Could you please help me out with the syntax for a similar query that filters for clients created week-to-date:

I have tried without success a number of syntax options including:

FIND Client WHERE WEEK(Client.CreationDate)=WEEK_DIFFERENCE(CURRENT_WEEK,0)

thanks,
Pete
aware_support
Posts: 7523
Joined: Sun Apr 24, 2005 12:36 am
Contact:

Post by aware_support »

Do you have a syntactic problem or the query doesn't work? In your query you are comparing the number of the week in a year with a number of weeks elapsed, which seems strange. Also, what database are you using?
Aware IM Support Team
pbrad
Posts: 781
Joined: Mon Jul 17, 2006 11:03 pm
Location: Ontario, Canada

Post by pbrad »

I guess that I am just getting myself confused and overlooking something very simple but:

I am running MySQL. I am trying to filter for clients that were created this week.

FIND Client WHERE WEEK(Client.CreationDate)= 45

returns a client that I entered this week but

FIND Client WHERE WEEK(Client.CreationDate)=CURRENT_WEEK doesn't return anything.

Please enlighten me O'Wise One.

cheers,
Pete
pbrad
Posts: 781
Joined: Mon Jul 17, 2006 11:03 pm
Location: Ontario, Canada

Post by pbrad »

Hi again,

Going on the assumption that you were going to give me an answer that made me feel stupid, I went back to the drawing board to prove to myself that I was stupid.

And the magic answer is:

FIND Client WHERE WEEK(Client.CreationDate)=WEEK(CURRENT_DATE)

Thanks,
Pete
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Post by tford »

Pete,

Thanks for sharing it with us O'Wise One

Tom
christopherb
Posts: 304
Joined: Fri Jun 22, 2007 8:26 pm

Post by christopherb »

Nice thank you....
Cheers,

Christopher
Post Reply