# Informix

View Only

## Calculate the ISO Week Number #### SangGyu JeongTue August 24, 2021 10:08 AM #### Paul WatsonTue August 24, 2021 10:34 AM #### Paul WatsonTue August 24, 2021 11:01 AM #### Paul WatsonTue August 24, 2021 12:15 PM #### Roland WintgenWed August 25, 2021 01:24 AM #### Øyvind GjerstadWed August 25, 2021 02:01 AM #### Roland WintgenWed August 25, 2021 05:07 AM #### Øyvind GjerstadWed August 25, 2021 05:34 AM #### SangGyu JeongWed August 25, 2021 03:29 AM • #### 1.  Calculate the ISO Week Number

Posted Tue August 24, 2021 10:08 AM
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
------------------------------

• #### 2.  RE: Calculate the ISO Week Number

Posted Tue August 24, 2021 10:34 AM

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= NULL;

strptime(dtestr,"%Y:%M:%D %H:%m:%s", &ptm);

}

return ((tm.tm_yday/7 + 1))

}

• #### 3.  RE: Calculate the ISO Week Number

Posted Tue August 24, 2021 11:01 AM

This compiles are runs

mi_integer oni_isoweek(mi_datetime *p_dte, MI_FPARAM *fp)

{

struct timeval   tv;

struct tm       *ptm;

char             dtestr;

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= 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;

• #### 4.  RE: Calculate the ISO Week Number

Posted Tue August 24, 2021 12:15 PM

Formatting the wrong way J

Use strptime(dtestr, "%F %R:%S", ptm)

• #### 5.  RE: Calculate the ISO Week Number

Posted Wed August 25, 2021 01:24 AM
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
------------------------------

• #### 6.  RE: Calculate the ISO Week Number

Posted Wed August 25, 2021 02:01 AM
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 = ".";
let p_week = mod(p_days - mod(p_days, 10), 100) / 10;
let p_week = mod(p_days, 10);
return (p_week);
end procedure;​``````

------------------------------
Developer/Architect
PostNord AS
------------------------------

• #### 7.  RE: Calculate the ISO Week Number

Posted Wed August 25, 2021 05:07 AM
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
------------------------------

• #### 8.  RE: Calculate the ISO Week Number

Posted Wed August 25, 2021 05:34 AM

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.

------------------------------
Developer/Architect
PostNord AS
------------------------------

• #### 9.  RE: Calculate the ISO Week Number

Posted Wed August 25, 2021 03:29 AM
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
------------------------------

• #### 10.  RE: Calculate the ISO Week Number

Posted Wed August 25, 2021 09:11 AM

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