#### Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

# How to get a date range for a week?

Posts: 161Member
edited 2011-05-26
Hello friends,

I have a question on date topic..

How to get a date range for a Week?

Example:
1. User has inputed the Year=2011 and Month=APR in the Options
2. Now I have to show in the report, week wise customer sales in the APR Month of 2011
3. For this I need to know what is the date range of Week1, Week2, Week3... so that I could apply the Week1 date range on the customer ledgers for Week1 sales and soon.

Your suggestions would greatly help in resolving this.

Awaiting on the replies.. Thanks in advance.
Dilip
Falling down is not a defeat..defeat is when you refuse to get up.

## Answers

• Posts: 2,993Member
just to make it clear, can you confirm this before we think about a solution?

Example: this month (may 2011)
you want to have these ranges (american notation)
1. 05/01/11..05/01/11
2. 05/02/11..05/08/11
3. 05/09/11..05/15/11
4. 05/16/11..05/22/11
5. 05/23/11..05/29/11
6. 05/30/11..05/31/11

Correct?
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
• Posts: 161Member
Exactly Mirko :-k
Dilip
Falling down is not a defeat..defeat is when you refuse to get up.
• Posts: 861Member
Use Date table as data item with "Period Type" Week.
• Posts: 2,993Member
vijay_g wrote:
Use Date table as data item with "Period Type" Week.
With this approach, you won't get the first and the last period of my example. (only full weeks are took into account)
but indeed, you have to manage the date table (i need some time to think about it, maybe on lunch break, because i'm busy :whistle: )
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
• Posts: 1,023Member
Belias wrote:
vijay_g wrote:
Use Date table as data item with "Period Type" Week.
With this approach, you won't get the first and the last period of my example. (only full weeks are took into account)
but indeed, you have to manage the date table (i need some time to think about it, maybe on lunch break, because i'm busy :whistle: )
why not?
ending date > first of the month & starting date < first of the next month.
You'll get all the weeks you need. A check of the first and last week should be enough to have all the filters one needs.
|Pressing F1 is so much faster than opening your browser|
|To-Increase|
• Posts: 2,993Member
Sorry, I meant, filtered date table is not enough...I wrote: "but indeed, you have to manage the date table".

I think we have to play with:
-first day of month/first day of first week
-last day of last week/last day of month

Sog, you meat this, didn't you?
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
• Posts: 161Member
Mirko and Sog,

I was just wondering if I could not arrive at exactly the ranges mentioned by Mirko in his example
Dilip
Falling down is not a defeat..defeat is when you refuse to get up.
• Posts: 2,993Member
gdkve9 wrote:
Mirko and Sog,

I was just wondering if I could not arrive at exactly the ranges mentioned by Mirko in his example
What does it mean? :?
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
• Posts: 161Member
I am struggling to arrive at all the date ranges of all the weeks in a month :?
Dilip
Falling down is not a defeat..defeat is when you refuse to get up.
• Posts: 861Member
Belias wrote:
Sorry, I meant, filtered date table is not enough...I wrote: "but indeed, you have to manage the date table".

I think we have to play with:
-first day of month/first day of first week
-last day of last week/last day of month

Sog, you meat this, didn't you?

Exactly..
it would be better to insert all week in temp table(date).
is not this right?
• Posts: 1,023Member
Fahn
Pseudocode
``````date.setrange(type,date.type::week);
date.setfilter("ending date",'>%1',the first of the month);//american thingy=05/31/2011
date.setfilter("starting date",'<%1',the last of the month);//american thingy=05/01/2011
if date.findset then
repeat
if date."starting date" < the first of the month then
date."starting date" := the first of the month;
if date."ending date" > the end of the month then
date."ending date" := the end of the month;
filter[x] := format to string(date."starting date") + .. + format to string(date."ending date");
until date.next = 0;
``````
filter[1] = 05/01/11..05/01/11
filter[2] = 05/02/11..05/08/11
filter[3] = 05/09/11..05/15/11
filter[4] = 05/16/11..05/22/11
filter[5] = 05/23/11..05/29/11
filter[6] = 05/30/11..05/31/11

btw Belias what do you mean with: Sog you mea(n)t this? I'm much more of a cheesy person
|Pressing F1 is so much faster than opening your browser|
|To-Increase|
• Posts: 2,993Member
edited 2011-05-26
Sog wrote:
``````date.setrange(type,date.type::week);
date.setfilter("ending date",'>%1',the first of the month);//american thingy=05/31/2011
date.setfilter("starting date",'<%1',the last of the month);//american thingy=05/01/2011
.....
``````
btw Belias what do you mean with: Sog you mea(n)t this? I'm much more of a cheesy person

...did you try to do these filters on a form based on date? you'll get only 4 lines, so your repeat/until loop will create an array of 4 element max (i know it's pseudo code, but i want to avoid misunderstandings). That's why i said that we have to play around a bit...

P.S.: i prefer meat, anyway, i don't like cheese
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
• Posts: 161Member
Wonderful Sog =D>

I could now understand what Mirko was saying about "Playing with date" :thumbsup:

Thankyou all guys
Dilip
Falling down is not a defeat..defeat is when you refuse to get up.
• Posts: 1,023Member
I've tested this out quickly and I get 6 lines.
But to be entirely correct it should be <= and >= for the starting/ending date filters
|Pressing F1 is so much faster than opening your browser|
|To-Increase|
• Posts: 2,993Member
#-o
i was filtering Starting date 010511..310511
what a fool! ](*,)
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
• Posts: 1,032Member
Even with CALCDATE that's possible. Must be something like this...
``````MyDate := TODAY;
REPEAT
DateExpression := STRSUBSTNO('<-CM + %1W>',i);

FirstDate := CALCDATE('<-CW>',CALCDATE(DateExpression,MyDate));
IF FirstDate < CALCDATE('<-CM>',MyDate) THEN
FirstDate := CALCDATE('<-CM>',MyDate);

LastDate := CALCDATE('<CW>',CALCDATE(DateExpression,MyDate));
IF LastDate > CALCDATE('<CM>',MyDate) THEN
LastDate := CALCDATE('<CM>',MyDate);

MESSAGE('%1..%2',FirstDate,LastDate);
i += 1;
UNTIL (LastDate = CALCDATE('<CM>',MyDate));
``````
"Money is likewise the greatest chance and the greatest scourge of mankind."
• Posts: 92Member
Even with CALCDATE that's possible. Must be something like this...
``````MyDate := TODAY;
REPEAT
DateExpression := STRSUBSTNO('<-CM + %1W>',i);

FirstDate := CALCDATE('<-CW>',CALCDATE(DateExpression,MyDate));
IF FirstDate < CALCDATE('<-CM>',MyDate) THEN
FirstDate := CALCDATE('<-CM>',MyDate);

LastDate := CALCDATE('<CW>',CALCDATE(DateExpression,MyDate));
IF LastDate > CALCDATE('<CM>',MyDate) THEN
LastDate := CALCDATE('<CM>',MyDate);

MESSAGE('%1..%2',FirstDate,LastDate);
i += 1;
UNTIL (LastDate = CALCDATE('<CM>',MyDate));
``````

With this piece of code I can get the last week range ?
Sign In or Register to comment.