Sql query to get week number where week starts with monday

Question

0.00/5 (No votes)

See more:

Hi Team,

i tried many ways to get week number for below requirement but didn't success.

Requirement:
First day of the week starts from :Monday
So it has to get week number as below.
28thDec2015 - 3rdJan2016 => Week 1
4thJan2016 - 10thJan2016 => Week 2
soo on..
based on above for a given input it has give appropriate week number.

What I have tried:

SQL

DECLARE @date DATETIME
SET @date='2016-12-28'year 2009
SET DATEFIRST 1
SELECT @@DATEFIRST AS DATEFIRST,DATEPART(WEEK,@date) AS WeekNo
----
set DATEFIRST 1
SELECT DATEPART( wk, '2015-12-28')
---
select DATEDIFF(dd,'2016-01-01','2016-02-29')/7+1
----

Posted 29-Feb-16 3:01am

indyarock

Updated 29-Feb-16 17:58pm

Add a Solution

Comments

PIEBALDconsult 29-Feb-16 9:11am

That description does not comply with the ISO definition for the week of the year;
A week that runs "28thDec2015 - 3rdJan2016" would be the last week of the previous year.
I suggest you rethink.

indyarock 1-Mar-16 1:47am

Hi,
Yes, we are aware of it and current logic is displaying ISOWeek(select DATEDIFF(dd,'2016-01-01','2016-02-29')/7+1) but user requirement is as given :(

PIEBALDconsult 1-Mar-16 8:35am

You should educate your user.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

snorkie · Answer 1 · 2016-02-29T05:03:00

You were very close to the answer. To implement the custom logic you want, you can create a stored procedure or function. Use the following tsql as an example. Basically use the DateDiff SQL function, pass it "wk" for week and then the start and end date and SQL server will do all the work for you.

SQL

DECLARE @date DATETIME,
		@Date2 datetime
SET @date='2015-12-28'

--Couple days later is same week, so 0. Could add +1 to answer if you want 
--to start at 1
SET @date2='2015-12-29'
SELECT DATEDIFF ( wk , @date , @date2 )

--The second week starting at Monday.
SET @date2='2016-1-3'
SELECT DATEDIFF ( wk , @date , @date2 )

--Farther in the future.
SET @date2='2016-5-28'
SELECT DATEDIFF ( wk , @date , @date2 )

johnwalker10 · Answer 2 · 2016-02-29T17:58:00

Solution 2

SQL

DECLARE @DATE DATETIME
SET @DATE = '2015-12-28'

SELECT DATEPART(WEEK, @DATE)  -
DATEPART(WEEK, DATEADD(MM, DATEDIFF(MM,0,@DATE), 0))+ 1 AS WEEK_OF_MONTH

Posted 29-Feb-16 17:58pm

johnwalker10

Comments

indyarock 1-Mar-16 4:04am

Hi,

it gives incorrect result as 4.