Week-Function in SQL ?
Hi,
is there a "week-function" in the Informix-SQL ? Something like
SELECT start_date, week(start_date) FROM ...
=>
start_date week
2.12.1999 48
...or has anyboy an idea to do this via a SPL Sequence ?
Thanks
Markus
> is there a "week-function" in the Informix-SQL ? Something like
>
> SELECT start_date, week(start_date) FROM ...
I didn't find any week(date) function, but here is a
select to simulated that one:
SELECT
CASE
WHEN (weekday(mdy(1,1,YEAR(TODAY))) = 1) -- Monday first day of
Year
THEN (((CURRENT YEAR TO DAY) - (MDY(1,1,YEAR(TODAY)))) / 7)
ELSE (((CURRENT YEAR TO DAY + 7 UNITS DAY) -
(MDY(1,1,YEAR(TODAY)))) / 7)
END
FROM any_table...
You can easily convert the statement above to an SPL,
hth,
Chris
Markus Buß schrieb:
>
> Hi,
>
> is there a "week-function" in the Informix-SQL ? Something like
>
> SELECT start_date, week(start_date) FROM ...>
> =>
>
> start_date week
> 2.12.1999 48
>
> ...or has anyboy an idea to do this via a SPL Sequence ?
>
> Thanks
> Markus
Hi Markus,
some time ago I wrote the following procdures that will convert date to year
and vice versa. These routines work fine and also deal with the 29th Feb. 2000.
You may need to slightly adjust the formats.
-- convert a date to week in format "yyyy.ww"
create procedure date2week(_date date) returning char(7);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;
-- convert a week in format "yyyy.ww" to date
create procedure week2date (_week char(7)) returning date;define p_date date;
define p_week char(7);
let _week = ext_week(_week);
if (_week = "0000.00")
then let p_date = 0;
elif (_week = "9999.99")
then let p_date = "31.12.9999";
else if (_week > last_week(_week[1,4]))
then let _week = last_week(_week[1,4]);
end if;
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));
end if;
return (p_date);
end procedure;
-- extend 5 digit week ("yy.ww") to 7 digit week ("yyyy.ww")
create procedure ext_week(_week char(7)) returning char(7);define p_week char(7);
if ((_week is null) or (_week = " ") or (_week = " . ") or (_week = " .
"))
then let p_week = date2week(today);
else if ((_week = "00.00") or (_week = "0000.00"))
then let p_week = "0000.00";
elif ((_week = "99.99") or (_week = "9999.99"))
then let p_week = "9999.99";
elif (length(_week) = 5)
then let p_week = " ";
let p_week[7] = _week[5];
let p_week[6] = _week[4];
let p_week[5] = _week[3];
let p_week[4] = _week[2];
let p_week[3] = _week[1];
if (p_week[3,4] > 50)
then let p_week[1,2] = 19;
else let p_week[1,2] = 20;
end if;
else let p_week = _week;
end if;
end if;
return (p_week);
end procedure;
-- retrieve the last week of a year in format "yyyy.ww"
create procedure last_week(_year smallint) returning char(7);define p_kw char(7);
define p_last_day smallint;
if ((_year is null) or (_year = 0))
then let p_kw = "0000.00";
else let p_last_day = 31;
let p_kw = date2week(mdy(12, p_last_day, _year));
while (p_kw[6,7] = 1)
let p_last_day = p_last_day - 1;
let p_kw = date2week(mdy(12, p_last_day, _year));
end while;
end if;
return (p_kw);
end procedure;
-- example
select date2week(today)
from systables
where tabname = "systables"
-- will return 1999.48
--
Roland Wintgen (Systemadministrator) ### # # ###
# # # # #
EVG Martens GmbH & Co. KG Tel. : 02166/550823 ##### # # # ###
Trompeterallee 244-246 Fax : 02166/550890 # # # # #
41189 Mönchengladbach e-mail: rw@evg.de ### # ###
Hello, first, thanks to all for answering ! From the posted articles I've made my own solution, because I needed something small & fast :) Here it is: ... trunc( (DAY_X - mdy(1,1,year(DAY_X)) + weekday(mdy(1,1,year(DAY_X)) - 1 ) / 7 ) ... it returns the number of the week (it beginns with Monday for me). Only Problem by 1.1.2000: return-value is 0, but it should be counted to week 52 of 1999... I've solved this with a small SPL :) bye Markus