Experts Exchange connects you with the people and services you need so you can get back to work.
Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
Open in new window
SET DATEFIRST 1 --<<-- chg as needed during testing
--note that the first column often changes with DATEFIRST but the second never does
DECLARE @year int
SET @year = 2011 --<<-- chg as needed to test different yrs
DATEPART(WEEK, date) AS Datepart_Week,
(DATEDIFF(DAY, 0, date) / 7) -
(DATEDIFF(DAY, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, date), 0)) / 7) + 1 AS Computed_Week
SELECT CAST(CAST(@year AS char(4)) + '0101' AS datetime) AS date UNION ALL
SELECT CAST(@year AS char(4)) + '0102' UNION ALL
SELECT CAST(@year AS char(4)) + '0103' UNION ALL
SELECT CAST(@year AS char(4)) + '0107' UNION ALL
SELECT CAST(@year AS char(4)) + '0108' UNION ALL
SELECT CAST(@year AS char(4)) + '0109'
--<<-- add additional dates if desired
) AS test_data
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.