Re: week no. for a date
Roland Wintgen wrote:
>
>
>Octav Chiriac schrieb:
>>
>> Girish Kagrana wrote:
>> >How Can I find WEEK NO. for a given date.
>> >
>> >For eg.
>> >
>> >Date is in MM/DD/YY format
>> >
>> >Date WEEK NO. for the YEAR
>> >----- ------------
>> >01/04/98 - 1
>> >01/05/98 - 2
>> >12/29/98 - 53
>> >
>> >TIA
>> >
>> >Girish
>> >
>> >
>> >
>>
>> DEFINE d DATE;
>> DEFINE w SMALLINT;
>>
>> LET w = (d - MDY(1,1,YEAR(d)))/7
>>
>> OR
>>
>> SELECT (myDate-MDY(1,1,YEAR(myDate)))/7
>> ...
>>
>> Hope it helps,
>> Octav
>>
>
>I think this is not correct, because your code will return 52 for
>12/29/98,
>whereas the correct value should be 53.
>
>I've written two Stored Procedures some time ago to handle the
>conversion
>from dates to weeknumbers and vice versa. These should return the
>correct
>values for any date and also handle the year 2000 correctly as a leap
>year.
>
>Note that the routines expect the week as a string "yyyy.ww", whereas
>yyyy
>means the year and is separated by the week number by a dot.
>
>Any further suggestions and enhancements are welcome.
>
>Bye
>
>Roland
>
>
>
>create procedure date2week(_date date) returning char(7); -- "yyyy.ww">
>define p_day smallint;
>define p_month smallint;
>define p_year smallint;
>define p_firstweekday smallint;
>define p_days smallint;
>define p_week char(7);
>
> if (_date is null)
> then let _date = today;
> end if;
> let p_week = " ";
> let p_day = day(_date);
> let p_month = month(_date);
> let p_year = year(_date);
> let p_firstweekday = weekday(mdy(1, 1, p_year));
> let p_days = (_date - mdy(1, 1, p_year) + p_firstweekday - 1) / 7;
> if (p_firstweekday > 4)
> then if (p_days = 0)
> then let p_week = date2week(mdy(12, 31, p_year - 1));
> let p_days = p_week[6,7];
> let p_year = p_year - 1;
> else if ((p_firstweekday = 7) and ((mod(p_year, 4) = 0) and
>((mod(p_year, 100) > 0) or (mod(p_year, 400) = 0))) and (p_month = 12)
>and (p_day = 31))
> then let p_days = 1;
> let p_year = p_year + 1;
> end if;
> end if;
> else if (_date >= mdy(12, 29, p_year))
> then let p_firstweekday = weekday(mdy(12, p_day, p_year));
> if (p_day = 31)
> then if ((p_firstweekday = 1) or (p_firstweekday = 2) or
>(p_firstweekday = 3))
> then let p_days = 0;
> let p_year = p_year + 1;
> end if;
> elif (p_day = 30)
> then if ((p_firstweekday = 1) or (p_firstweekday = 2))
> then let p_days = 0;
> let p_year = p_year + 1;
> end if;
> elif (p_day = 29)
> then if (p_firstweekday = 1)
> then let p_days = 0;
> let p_year = p_year + 1;
> end if;
> end if;
> end if;
> let p_days = p_days + 1;
> end if;
> let p_week[1,4] = p_year;
> let p_week[5] = ".";
> let p_week[6] = mod(p_days - mod(p_days, 10), 100) / 10;
> let p_week[7] = mod(p_days, 10);
> return (p_week);
>end procedure;
>
>create procedure week2date (_week char(7)) returning date;>
>define p_date date;
>define p_week char(7);
>
> let p_date = mdy(1, 1, _week[1,4]);
> let p_week = date2week(p_date);
> while (_week[1,4] > p_week[1,4])
> let p_date = p_date + 7;
> let p_week = date2week(p_date);
> end while;
> let p_date = p_date + (7 * (_week[6,7] - 1));
> return (p_date);
>end procedure;
>
Sure I'm wrong. I have write this statements just as a guidance,
as a template, no more.
Sorry,
Octav
Happy New Year!!!
--
Octav Chiriac Phone: (373) 2 21 20 96
NetInfo S.R.L. Fax: (373) 2 21 36 59
Chisinau (373) 2 24 00 83
Moldova, Republic of mailto:com@netinfo-moldova.com