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
------------------------------
Original Message:
Sent: Tue August 24, 2021 10:08 AM
From: SangGyu Jeong
Subject: Calculate the ISO Week Number
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 iwFROM(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