Re: week no. for a date
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 -- 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
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;
--
__ _______________
AMIGA /// /Roland Wintgen\\
fever/// /------ \\|/ ------\\ Goldregenweg 3, D-41844 Wegberg,
GERMANY
__ /// /------- o o -------\\
\\\\\\/// /-----oOO-(_)-OOo-----\\ e-mail: rolle@corona.oche.de
(preferred)
\\XX/ /-"The Invisible FREAK"-\\ roland.wintgen@t-online.de
~~~~~~~~~~~~~~~~~~~~~~~~~
"Be yourself, no matter what they say."
In article <368A0F3D.4F14BE61@evg.de>, Roland Wintgen <info@evg.de>
writes
>
>
>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
>> >
We store the week start and end dates in table
create table week_dates
(
week_no integer,
week_start_date date,
week_end_date date
)
create unique index wd1 on week_dates(week_no)
and do
select week_no from week_dates where my_date between week_start_date
and week_end_date;
Simple and allows do to define 52/53/54 week years if that is what the
customer wants.
>> >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;
>
>
--
David Williams