Sql-server – Find Iteration of Day of Week in Month

sql serversql-server-2016t-sql

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'?

Best Answer

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)

Related Question