sql - ODBC function for calendar week? -

question on odbc functions:

i need iso 8601 calendar week sql datetime. in sql-server, syntax this:

select datepart(iso_week, getdate())  

in sql server 2005, iso_week doesn't exist, have use function this: http://blogs.lessthandot.com/index.php/datamgmt/datadesign/iso-week-in-sql-server

now question: in order keep database-independant, possible iso-calendar week via odbc function ?

odbc-functions (except getdate of course):

select   getdate() vt_sql_datetime_nondeterministic_function   ,{ fn now() } vodbc_datetime_canonical_function  ,{ fn curdate() } vodbc_dateonly_canonical_function  ,cast(floor(cast(getdate() float)) datetime) vfloor_mine  ,convert(char(8),  { fn now() }, 112) v112_iso  ,convert(char(10), { fn now() }, 104) v104_thomas  ,{ fn convert({fn curdate()}, sql_date)} vodbc_proper   -- testing odbc functions & syntax ,{ fn concat('abc', 'def')} odbc_concat ,{ fn concat(null, 'def')}  odbc_concat_nullleft ,{ fn concat('abc', null)}  odbc_concat_nullright ,{ fn concat(null, null)}   odbc_concat_nullboth ,{ fn length('abc')}    odbc_length ,{ fn ucase('abc')} odbc_ucase ,{ fn lcase('abc')} odbc_lcase ,{ fn substring('test me', 1, 4)} odbc_substring  ,{ fn locate('in', 'needle in haystack')}  odbc_locate  ,{ fn substring('test me', 1, { fn locate(' me', 'test me') } - 1)} odbc_substring_locate 

edit: (by question asker)
, resulting odbc equivalent, actual answer question:

({fn dayofyear({fn timestampdiff(sql_tsi_day, 0, dt) } / 7 * 7 + 3)}+6) / 7 odbc_iso_week 

-- end edit

here better way iso_week in sql-server 2005 or 2000

create function f_isoweek(@date datetime)  returns int   begin   return (datepart(dy, datediff(d, 0, @date) / 7 * 7 + 3)+6) / 7   end  

read more here:

isoweek in sql server 2005

edit: after claimed script failed, added script proves works:

--this script run on mssql server 2008,  --it show rows isoweek calculated wrong given formular (0 rows)  ;with ( select cast('1900-01-01' datetime) d union select dateadd(day, 1, d) d < '2100-01-01' ) select count(*)   (datepart(dy, datediff(d, 0, d) / 7 * 7 + 3)+6) / 7 <> datepart(iso_week, d) option (maxrecursion 0)