Formatting date

[mikkom]

I have date in form year and week, eg. 200452. How I can get first day of that week in date form, eg. 12/20/2004?

 

[Madawc]

ToText({your.date}, "MM/dd/yyyy")

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[briangriffin]

Not sure that will work; just in case:

whileprintingrecords;
numbervar v_year := tonumber(left(totext({yourdatefield},"#"),4));
numbervar v_dayofyear := tonumber(right(totext({yourdatefield},"#"),2)) * 7;
datevar v_date := date(v_year,1,1) + v_dayofyear;

v_date - dayofweek(v_date) + 2

This will work if the format of dates earlier in the year contains the leading zero( 201002).

Also, this formula returns 12/27/2004, which is the Monday of the last week in December 2004. You specified 12/20/2004 - do you need the Monday of the last full week?

 

[lbass]

mikkom needs to identify the rule for what constitutes the first week of the year. Is it the week containing January 1? The first week with at least four days? The first full week?

Also, can we assume that the weeks starts with Sunday?

-LB

 

[MCuthill]

LB,

I read this query as the opposite of one you answered earlier this week (though I lack the smarts to modify your previous solution).

Post: How to determine weekending date for a given date?

Your solution:

lbass (TechnicalUser) 21 Dec 10 16:44

{table.date}-dayofweek({table.date})+7

Only I believe mikkom is wanting to return the first day of the week as opposed to the last -- though one would still need to know if that is calendar week or business week.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

Mike,

Your approach very well might be correct for mikkom's purpose--I don't know, because I don't know how weeks are being counted or whether it is a Sunday or Monday week start, etc.

-LB

 

[MCuthill]

Hey LB!

Oh yeah! *face palm*

Totally forgot that there is also the "Fiscal Week" versus "Calendar Week" issue as well as the day to consider the start of the week.

Yet another reason I defer to your wisdom. *big smile*

I suppose we will just need await mikkom's reply.

Thanks LB!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[mikkom]

I need to identify the rule for what constitutes the first week of the year. And the weeks starts with monday.

For example, in year 2004 monday of the first week is 12/29/2003.

How I can identify rule to do this?

 

[lbass]

Well, how do YOU know that's the first week of 2004?

-LB

 

[briangriffin]

I think this will work for you now; you can test by changing the 200452 to other values.

whileprintingrecords;
numbervar v_year := tonumber(left(totext(200452,"#"),4));
numbervar v_weeknumber := tonumber(right(totext(200452,"#"),2)) - 1;
datevar v_firstMonday := date(v_year,1,1);

v_firstMonday :=  v_firstMonday - dayofweek(v_FirstMonday) + 2;

datevar v_displaydate := date(dateadd("ww",v_weeknumber,v_FirstMonday));

v_displaydate

 

[mikkom]

Thanks, that code seems to calculate date correct.

But I still have a problem with my report. It says that formula {@next} cannot be used, because it must be evaluated later.

in selection formula
{@next} in {?start_date} to {?end_date}

{@next}
datevar nextdt := {@start};
numbervar i := {FU.I};
while nextdt <= today
do (nextdt := cdate(dateadd("ww",i,nextdt)));
nextdt

{@start}
whileprintingrecords;
numbervar v_year := tonumber(left(totext({@vuosi+viikko}),4));
numbervar v_weeknumber := tonumber(right(totext({@vuosi+viikko}),2)) - 1;
datevar v_firstMonday := date(v_year,1,1);
v_firstMonday := v_firstMonday - dayofweek(v_FirstMonday) + 2;
datevar v_displaydate := date(dateadd("ww",v_weeknumber,v_FirstMonday));
v_displaydate