14,177,621 members
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.
Posted
Updated 13-Mar-19 6:14am

## 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!

## 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[^]

## 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`

Top Experts
Last 24hrsThis month
 Richard MacCutchan 165 Patrice T 60 TheRealSteveJudge 60 Gerry Schmitz 55 Maciej Los 50
 OriginalGriff 4,674 Richard MacCutchan 2,155 Patrice T 1,577 Gerry Schmitz 1,307 phil.o 1,268