Click here to Skip to main content
14,177,621 members
Rate this:
Please Sign up or sign in to vote.
See more:
I am trying to get my calendar dimension to properly calculate the weeks of the year which ISO_week seemed to be a solution yet I am in the USA and our weeks start on Sunday. Is there a way to get the weeks to start on Sundays while still having end/start of years being in proper weeks like how ISO_WEEK calculates it? My current Add to dictionary starts on Sundays yet has a 53rd week every year because I don't have an indepth formula. Currently I'm using
DATEPART(WW, @CurrentDate) AS week_of_year,

Background information on @CurrentDate would be

DECLARE @StartDate DATETIME = '01/04/2010' --Starting value of Date Range
DECLARE @EndDate DATETIME = '01/01/2050' --End Value of Date Range
DECLARE @CurrentDate AS DATETIME = @StartDate
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
SET @CurrentYear = DATEPART(YY, @CurrentDate)
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)

What I have tried:

Currently for ISOweek I have:
[isoweek] AS DATEPART(ISO_WEEK, [date_day]),

Date_day is just date
I have also tried
[iso_week_of_year] TINYINT NULL,
iso_week_of_year = CONVERT(TINYINT, isoweek),

These just return the weeks starting on Monday as expected.
Updated 13-Mar-19 6:14am
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Before you start doing that, you need to define your "rules" for Week Number - the ISO specification lays them down very solidly for Monday weeks: ISO week date - Wikipedia[^] and is heavily dependant on the week in which the Thursday falls - and as a result an "ISO year" can have between 364 and 371 days depending on the first Thursday of the "calendar" week - meaning that Jan 1st can be in Week 52 or 53 of the previous year, or week 1 of "this" year.

You would need to first work out exactly when your year is going to start, and go from there!
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

My recommendation would be to review some of the SQL Server methods that were used before ISO_Week was added to do the calculation; and then create a new function based on a slightly tweaked version of what's out there.

I would recommend reviewing this article over on Sql Server Central, looks like it may be working a Tally-Table which generally operates at a higher-performance than other variations.
A Simple Formula to Calculate the ISO Week Number - SQLServerCentral[^]
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

What ended up working was literally so simple I can't believe I didn't see it earlier...
Just pushing the date worked wonderfully. Still have to validate data after long periods of time but weeks are starting on Sundays now instead of Mondays!
DATEPART(ISO_WEEK, DATEADD(day,+1,@CurrentDate)) AS week_of_year

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Cookies | Terms of Service
Web01 | 2.8.190526.1 | Last Updated 20 Mar 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100