Calculate the ISO Week Number
SangGyu Jeong asked how to compute the ISO week number in Informix. Paul Watson posted an untested C UDR attempt, later corrected. Roland Wintgen and Oyvind Gjerstad posted an SPL procedure dating from comp.databases.informix, tracing to Jonathan Leffler. SangGyu wrote his own SPL, weeknum, which Roland recognised as a variant of that code. Paul timed it faster than his UDR on 10,000 rows.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello All,
I'm trying to convert to week number (1-53) for a specific date.
And I want to use ISO week.
I looked at the post below, but it didn't help much.
https://www.sqlservercentral.com/articles/a-simple-formula-to-calculate-the-iso-week-number
I wrote a messy query like the one below... but it seems inaccurate. Please advise on what is wrong or what needs to be improved.
Thanks,
SELECT ymd,
(d3 / 7 + 1)::int iw
FROM
(
SELECT dt
,TO_CHAR(dt, '%Y%m%d')::char(10) ymd
,(((TRUNC(dt - 1 units day, 'day') - NEXT_DAY(TRUNC(TRUNC(dt - 1 units day, 'day') - 1 units day, 'year') , 'SUN'))::interval day(3) to day)::varchar(12)::int ) d3
FROM (SELECT TO_DATE(yy||'01', '%Y%m') + (LEVEL - 1) units day dt
FROM (SELECT '2021' yy FROM dual)
CONNECT BY LEVEL <= 366)
)
------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------
#Informix
Write a C UDR - it will be quicker, something like this - pseudo code 100% not tested mi_integer oni_isoweek(mi_datetime *p_dte, MI_FPARAM *fp) { struct timeval tv; struct tm *ptm; dtestr char(26); if(mi_fp_argisnull(fp,0)==MI_TRUE) { gettimeofday(&tv, NULL); ptm=localtime(&tv.tv_sec); } else { dtestr=mi_datetime_to_string(p_dte); dtestr[19]= NULL; strptime(dtestr,"%Y:%M:%D %H:%m:%s", &ptm); } return ((tm.tm_yday/7 + 1)) }
This compiles are runs
mi_integer oni_isoweek(mi_datetime *p_dte, MI_FPARAM *fp)
{
struct timeval tv;
struct tm *ptm;
char dtestr[40];
gettimeofday(&tv, NULL);
ptm=localtime(&tv.tv_sec);
if(mi_fp_argisnull(fp,0) != MI_TRUE)
{
strcpy(dtestr, mi_datetime_to_string(p_dte));
dtestr[19]= 0 ;
strftime(dtestr, 40, "%Y:%M:%D %R:%S", ptm);
}
return ((ptm->tm_yday/7 + 1));
}
begin work;
drop function if exists oni_isoweek(datetime year to fraction(5));
create function oni_isoweek (datetime year to fraction(5))returns integer
with (not variant, handlesnulls, parallelizable)
external name "$INFORMIXDIR/extend/Oninit.1.0/oninit.bld(oni_isoweek)" language c;
grant execute on function oni_isoweek (datetime year to fraction(5)) to public;commit work;
select oni_isoweek(CURRENT) from sysmaster:sysdual;
select oni_isoweek(NULL) from sysmaster:sysdual;
Formatting the wrong way J Use strptime(dtestr, "%F %R:%S", ptm)
Take a look at the SPL Jonathan Leffler wrote back in 2001 here: How do I calculate the week number given a date? Kind regards ------------------------------ Roland Wintgen DBA, Genero/4GL Developer EVG Martens GmbH & Co. KG Moenchengladbach ------------------------------
I think this was posted to the old newsgroup comp.databases.informix many, many years ago. Can't find the original posting now, so I don't know who the original author is, perhaps Jonathan Leffler? create or replace procedure "informix".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; ------------------------------ Øyvind Gjerstad Developer/Architect PostNord AS ------------------------------
Thanks to everyone replied.
After Googling more related materials, I made the SPL again.
create function weeknum ( dt varchar(10) )
returning int;define wknum int;
select trunc(((to_date(dt, '%Y-%m-%d') - yearstart)::interval day(3) to day::varchar(12)::integer) / 7) + 1
into wknum
from
(
select case when nextyr <= to_date(dt, '%Y-%m-%d') then nextyr
when curryr <= to_date(dt, '%Y-%m-%d') then curryr
else prioryr
end yearstart
from
(
select
mdy(1,1,1753) + trunc(((jan4 - 1 units year - mdy(1,1,1900) + 53690 units day)::varchar(12)::integer) / 7) * 7 units day prioryr,
mdy(1,1,1753) + trunc(((jan4 - mdy(1,1,1900) + 53690 units day)::varchar(12)::integer) / 7) * 7 units day curryr,
mdy(1,1,1753) + trunc(((jan4 + 1 units year - mdy(1,1,1900) + 53690 units day)::varchar(12)::integer) / 7) * 7 units day nextyr
from (
select mdy(1,1,1900) + (extend (to_date(dt, '%Y-%m-%d'), year to year) - mdy (1,1,1900))::varchar(12)::integer units year + 3 units day jan4 from dual
)
)
);
return wknum;
end function;
$ dbaccess test -
> select weeknum('2021-08-25') from sysmaster:sysdual;
(expression)34
1 row(s) retrieved.
------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------
Thats funny because your code seems to be my updated version of Jonathans original procedure I posted above. I've been using this SPL for many years now and it still works flawlessly. Kind regards ------------------------------ Roland Wintgen DBA, Genero/4GL Developer EVG Martens GmbH & Co. KG Moenchengladbach ------------------------------
Ah, yes, I was able to track it down, it appears that this is the version you (Roland Wintgen) posted to comp.databases.informix on January 4th, 1999. We have been using this for many years. I'm sorry, I didn't see your post regarding Jonathan Leffler's version before I posted. ------------------------------ Øyvind Gjerstad Developer/Architect PostNord AS ------------------------------
Cos I was curious, processing 10,000 rows
time dbaccess eric oni_isoweek.sql >/dev/null 2>&1
real 0m1.444s
user 0m0.026s
sys 0m0.017s
time dbaccess eric weeknum.sql >/dev/null 2>&1
real 0m0.370s
user 0m0.028s
sys 0m0.016s
Cheers
Paul