ODBC function for calendar week?

Question

Question on ODBC functions:

I need to get the ISO 8601 calendar week from a SQL datetime. In SQL-Server, the syntax for that is this:

SELECT DATEPART(ISO_WEEK, GETDATE())

in SQL server 2005, ISO_WEEK doesn't exist, so I have to use a function like this: http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/iso-week-in-sql-server

Now my question: In order to keep it database-independant, is it possible to get the ISO-calendar week via a ODBC function ?

ODBC-Functions like this (except getdate of course):

SELECT 
 GETDATE() AS vT_SQL_DateTime_NonDeterministic_Function 

,{ fn NOW() } AS vODBC_DateTime_Canonical_Function 
,{ fn CURDATE() } AS vODBC_DateOnly_Canonical_Function 
,CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime) AS vFloor_Mine 
,CONVERT(char(8),  { fn NOW() }, 112) AS v112_ISO 
,CONVERT(char(10), { fn NOW() }, 104) AS v104_Thomas 
,{ fn CONVERT({fn CURDATE()}, SQL_DATE)} AS vODBC_Proper 

-- Testing ODBC functions & syntax
,{ fn CONCAT('abc', 'def')} AS ODBC_Concat
,{ fn CONCAT(NULL, 'def')}  AS ODBC_Concat_NullLeft
,{ fn CONCAT('abc', NULL)}  AS ODBC_Concat_NullRight
,{ fn CONCAT(NULL, NULL)}   AS ODBC_Concat_NullBoth
,{ fn LENGTH('abc')}    AS ODBC_Length
,{ fn UCASE('abc')} AS ODBC_UCASE
,{ fn LCASE('ABC')} AS ODBC_LCASE
,{ fn SUBSTRING('Test me', 1, 4)} AS ODBC_SUBSTRING 
,{ fn LOCATE('in', 'needle in the haystack')}  AS ODBC_Locate 
,{ fn SUBSTRING('Test me', 1, { fn LOCATE(' me', 'Test me') } - 1)} AS ODBC_SUBSTRING_Locate

See my question http://stackoverflow.com/q/2220786/44522 and the accepted answer. Should help. — MicSim, Mar 08 '12 at 13:53
@MicSim: Nope, your question doesn't contain the answer, and it's not an ODBC function either. — Stefan Steiger, Mar 10 '12 at 07:49

score 3 · Accepted Answer · edited May 23 '17 at 12:00

3

Edit: (by question asker)
And this is the resulting ODBC equivalent, which is the actual answer to my question:

({fn DAYOFYEAR({fn TIMESTAMPDIFF(SQL_TSI_DAY, 0, dt) } / 7 * 7 + 3)}+6) / 7 AS ODBC_ISO_WEEK

-- End Edit

Here is a better way to get iso_week in sql-server 2005 or 2000

CREATE function f_isoweek(@date datetime) 
RETURNS INT 
as 
BEGIN 

RETURN (datepart(DY, datediff(d, 0, @date) / 7 * 7 + 3)+6) / 7 

END

Read more here:

Isoweek in SQL Server 2005

EDIT: After someone claimed my script failed, i added this script proves that it works:

--This script will run on mssql server 2008, 
--it will show all rows where isoweek is calculated wrong with the given formular (0 rows)

;with a as
(
select cast('1900-01-01' as datetime) d
union all
select dateadd(day, 1, d) from a
where d < '2100-01-01'
)
select count(*) 
from a 
where (datepart(DY, datediff(d, 0, d) / 7 * 7 + 3)+6) / 7
<> datepart(iso_week, d)
option (maxrecursion 0)

edited May 23 '17 at 12:00

Community

1
1

answered Mar 08 '12 at 18:52

t-clausen.dk

43,517
12
59
92

Yes, it's a simpler version. But I'm not sure if it's correct. Besides that: it's an SQL-Server function, not an ODBC function, which is what my question was about. – Stefan Steiger Mar 10 '12 at 07:41
@Quandary as you mention there is no iso_week in 2005, so you need a workaround, so i gave you the perfect syntax to find the iso_week. This works and it is really useful because of the fast execution time. This can easily be worked into your script. So drop your odbc function and use this. – t-clausen.dk Mar 10 '12 at 12:35
Quote Albert Einstein: You should always make it as simple as possible - but not any simpler. In other words, the reason why your function is shorter (or seemingly more elegant) is based in the fact that it doesn't take handle all possible dates. For example, I just tested, and it fails on 29.02.2000... But I just tested and realized this is also true for the function I referenced. It returns 9 isntead of 10... (well, actually it could also be possible that the SQL server function is wrong - I don't know...) – Stefan Steiger Mar 10 '12 at 14:29
@Quandary I assure you it works, try this select (datepart(DY, datediff(d, 0, '2000-02-29') / 7 * 7 + 3)+6) / 7, datepart(iso_week, '2000-02-29') Both returns 9 and 9 is the right answer – t-clausen.dk Mar 10 '12 at 19:48
Hmm, you're right, it seems like I have been dreaming when doing the copy-paste. Sorry, my fault. I added the ODBC equivalent to your answer and accepted. :) – Stefan Steiger Mar 11 '12 at 17:22

score -1 · Answer 2 · answered Mar 08 '12 at 16:07

-1

haven't worked with 2005 for a while, but wouldn't this do the trick?

SELECT DatePart(week, GETDATE())

you might want to ensure that your SQL Server is set to start the week on Monday (SET DATEFIRST) to match the ISO logic.

answered Mar 08 '12 at 16:07

rafael Lenartowicz

34
1

No, ISO week, not week. DateFirst ==> Monday won't correctly calculate the ISO week number in all cases. And besides, I was asking for an ODBC function, not an SQL-Server function... – Stefan Steiger Mar 10 '12 at 07:44

ODBC function for calendar week?

2 Answers2