CODEWITHSUNDEEP
sqlsql-servert-sqldate
BIDeveloper
BIDeveloper

Reputation: 847

Week No Of Year Starting from 1st Date of Year

I have a requirement where the week of year starts from 1st of every Jan similar for Week of Month Starts from 1st of Every Month as shown in image

enter image description here

I had write below SQL code for Week of Month, but not able to go through the Week of Year

SQL Statement for Week of Month

  Select [Week no]=case when DATEPART(dd,Date_Value) between 1 and 7 then 1
        when DATEPART(dd,Date_Value) between 8 and 14 then 2
        when DATEPART(dd,Date_Value) between 15 and 21 then 3
        when DATEPART(dd,Date_Value) between 22 and 28 then 4
        when DATEPART(dd,Date_Value) >28 then 5
        end
        from Datecte

Can anybody help me in getting this?

Upvotes: 1

Views: 122

Answers (2)

Matt
Matt

Reputation: 15061

Using DATEPART, DATEADD & DATEDIFF functions this can be achieved.

SELECT date, 
DATEPART(wk, date) AS WeekofYear,
DATEDIFF(week, DATEADD(month, DATEDIFF(month, 0, date), 0), date) +1 AS WeekofMonth,
FROM Datecte

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Perhaps a simpler method for the week of the month is to do:

select (day(date_value) + 6) / 7 as weekOfMonth

The + 6 is because day() returns values between 1 and 7, and the weeks are numbered starting with 1. Note that SQL Server does integer division so the result is an integer.

You can do something similar for the year:

select (datediff(day, cast(datename(year, date_value) + '-01-01' as date), date_value) + 7) / 7 as weekOfYear

The date difference gets the day number of the year. The + 7 is just so the counting starts at 1 rather than 0.

Finally, if these values are important for you, then you can add computed columns to the table in question:

alter table t add WeekOfMonday as ((day(date_value) + 6) / 7)

Upvotes: 1

Related Questions