# Find Iteration of Day of Week in Month

I have a date dimension table in which I need to add a new column in which I define the iteration of the day of the week within the month (2 for the second Mon/Tue/Wed/Thu/Fri/Sat/Sun etc).

Is it possible to do this be making calculations solely on the date column of the table, which is of type 'date'?

It's actually surprisingly easy using `DAY()`, as 1-7 will be 1, 8-14 will be 2, 15-21 will be 3, 22-28 will be 4 and 29-31 will be 5. Doesn't matter what the name of the day is, that will always hold true.

``````select case when DAY(yourdate) <=7  then 1
when DAY(yourdate) <=14 then 2
when DAY(yourdate) <=21 then 3
when DAY(yourdate) <=28 then 4
else 5
end as occurance
from yourtable;
``````

(`case` short-circuits)

Alternatively you could calculate the result using a formula:

``````SELECT
(DAY(YourDate) + 6) / 7 AS Iteration
FROM
dbo.YourTable
;
``````

In cases where both operands of a division operation are integers, the result is also an integer, and a rounded down one at that.

So, `DAY(YourDate)` = 1 will give you 1, because

``````(1 + 6) / 7 = 7 / 7 = 1
``````

A 2 will yield

`````` (2 + 6) / 7 = 8 / 7 = 1
``````

– again 1.

If it is 7, you still get 1:

``````(7 + 6) / 7 = 13 / 7 = 1
``````

But 8 will result in 2:

``````(8 + 6) / 7 = 14 / 7 = 2
``````

And so on.