Isoweek in SQL Server 2005

by

There is a link here for other earlier attempts http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60510

This is the OLD code for the function

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

SELECT @rv = datediff(ww, dateadd(ww, datediff(d, 0, dateadd(yy, datediff(yy, 0, day4),3))/7,-4),day4)
FROM (SELECT dateadd(ww, datediff(day, 0, @date)/7, 3) day4) a

RETURN @rv
END

After combining @AndriyM ‘s brilliant answer with my own, we are down to 1 line. This is the NEW code.

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

RETURN (datepart(DY, datediff(d, 0, @date) / 7 * 7 + 3)+6) / 7
-- replaced code for yet another improvement.
--RETURN (datepart(DY, dateadd(ww, datediff(d, 0, @date) / 7, 3))+6) / 7

END

Explanation for the old code (not going to explain the new code. It is fragments from my code and AndriyM’s code):

Finding weekday 4 of the chosen date

dateadd(week, datediff(day, 0, @date)/7, 3)

Finding isoyear – year of weekday 4 of a week is always the same year as the isoyear of that week

datediff(yy, 0, day4)

When adding 3 days to the first day of the isoyear a random day of the first isoweek of the isoyear is found

dateadd(yy, datediff(yy, 0, day4),3)

finding relative week of the first isoweek of the isoyear

datediff(d, 0, dateadd(yy, datediff(yy, 0, day4),3))/7

Finding the monday minus 4 days of the first isoweek results in thursday of the week BEFORE the first day of the first isoweek of the isoyear

dateadd(ww, datediff(d, 0, dateadd(yy, datediff(yy, 0, day4),3))/7,-4)

Knowing first thursday of the week before the first isoweek
and first thursday of the chosen week,
makes it is quite easy to calculate the week, it doesn’t matter which setting datefirst has since the weekdays of both dates are thursdays.

datediff(ww, dateadd(ww, datediff(d, 0, dateadd(yy, datediff(yy, 0, day4),3))/7,-4),day4)

More Related Contents:

  1. Retrieving last record in each group from database – SQL Server 2005/2008
  2. Base64 encoding in SQL Server 2005 T-SQL
  3. How do I perform an accent insensitive compare (e with è, é, ê and ë) in SQL Server?
  4. Calculating timespan with t-sql
  5. Aggregate SQL Function to grab only the first from each group
  6. Get top 1 row of each group
  7. Referring to a Column Alias in a WHERE Clause
  8. Sql Server string to date conversion
  9. Convert Month Number to Month Name Function in SQL
  10. SET versus SELECT when assigning variables?
  11. Generate a resultset of incrementing dates in TSQL
  12. Getting result of dynamic SQL into a variable for sql-server
  13. How to get difference between two rows for a column field?
  14. Better techniques for trimming leading zeros in SQL Server?
  15. What’s the best way to select the minimum value from several columns?
  16. How to upsert (update or insert) in SQL Server 2005
  17. Delete duplicate records from a SQL table without a primary key
  18. How to get the last record per group in SQL
  19. Splitting delimited values in a SQL column into multiple rows
  20. SQL Server ORDER BY date and nulls last
  21. varbinary to string on SQL Server
  22. Executing dynamic SQL in a SQLServer 2005 function
  23. How can I determine the status of a job?
  24. SQL Server 2005 ROW_NUMBER() without ORDER BY
  25. How to delete all rows from all tables in a SQL Server database?
  26. Can an INNER JOIN offer better performance than EXISTS
  27. What is difference between datetime and timestamp
  28. How do I check if a Sql server string is null or empty
  29. SQL Server 2005 Computed Column Is Persisted
  30. Sql Server : How to use an aggregate function like MAX in a WHERE clause

Leave a Comment