How to calculate xx year XX week in SQL Server which days

SQL Server has a very powerful function: DATEPART ()

It can calculate that 2017-10-17 is part of this year's

Day of the week: Select DatePart (dy, ' 2017-10-17 ')

Week: Select DatePart (wk, ' 2017-10-17 ')

First month: Select DATEPART (mm, ' 2017-10-17 ')

First quarter: Select DATEPART (QQ, ' 2017-10-17 ')

Day of the Week: select (Case datepart (DW, ' 2017-10-17 ')

When 1 Then ' Sunday '

When 2 Then ' Monday '

When 3 Then ' Tuesday '

When 4 Then ' Wednesday '

When 5 Then ' Thursday '

When 6 then ' Friday '

When 7 Then ' Saturday '

End

So the question is: if we need to know what day the 25th week of 2017 is, how to write SQL

Analysis:

1. The 1th day of the 25th week of 2017 is a few months.

1.1 The 1th day of the 1th week of 2017 years is the week of 2017-1-1

1.2 7 days a week

1.3 2017-1-1 need to add how many Tian Deng in 2017 25th week 1th Day

So, the 1th day of the 25th week = 2017 1th Sunday + (25-2) *7,25-2 minus 1th Week and 25th week

2. The last 1 days of the 25th week of 2017 is a few months.

2017 25th Week last 1 days = 2017 25th Week 1th Day + 6 days

Here we know the key point is how to calculate the number of days in 2017 for 1 weeks, and the key to calculating the number of days is to determine whether Sunday is the 1th day of the week or the last day.

January 1, 2017 happens to be Sunday.

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M00/08/AC/wKiom1nlp2_AiAL8AAAeGF-tKKE461.png-wh_500x0-wm_ 3-wmp_4-s_417669323.png "title=" 20170101.PNG "alt=" Wkiom1nlp2_aial8aaaegf-tkke461.png-wh_50 "/>

If Sunday is the first day of the week

1th Day of the 25th week of 2017 = January 1, 2017 + 7 days + (25-2) * 7 days

SQL statement: DateAdd (day,7+ (25-2) *7, ' 2017-1-1 '), with the result: 2017-6-18

2017 The 25th week is: 2017-6-18 to 2017-6-24

If Sunday is the last day of the week

1th Day of the 25th week of 2017 = January 1, 2017 + 1 days + (25-2) * 7 days

SQL statement: DateAdd (day,1+ (25-2) *7, ' 2017-1-1 '), with the result: 2017-6-12

2017 The 25th week is: 2017-6-12 to 2017-6-18

Next, write the above calculation as an SQL statement

Sunday for the 1th day:

DECLARE @weekofyear int

DECLARE @date datetime

DECLARE @dayofweek int

DECLARE @firstday varchar (20)

DECLARE @firstweek int

Set @date = ' 2017-1-1 '

Select @dayofweek =datepart (DW, @date)

Set @weekofyear =25

Set @[email protected]

DECLARE @begin_date datetime

DECLARE @end_date datetime

Set @begin_date =dateadd (day, @firstweek + (@weekofyear-2) *7, @date)

Set @end_date =dateadd (day,6, @begin_date)

Sunday is the last day:

DECLARE @weekofyear int

DECLARE @date datetime

DECLARE @dayofweek int

DECLARE @firstweek int

Set @date = ' 2017-1-1 '

Select @dayofweek =datepart (DW, @date)

Set @weekofyear =25

if ([email protected]) >7

Begin

Set @[email protected]

End

Else

Begin

Set @[email protected]

End

DECLARE @begin_date datetime

DECLARE @end_date datetime

Set @begin_date =dateadd (day, @firstweek + (@weekofyear-2) *7, @date)

Set @end_date =dateadd (day,6, @begin_date)

The code is then consolidated into a stored procedure with the following results:

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/A7/5E/wKioL1nluFuzkziGAAAgDVKTOVo222.png "title=" Weeksofyear. PNG "alt=" Wkiol1nlufuzkzigaaagdvktovo222.png "/>

Result validation:

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/08/AF/wKiom1nlutyT2dbwAAAtyZkRHWE572.png "title=" 201701.PNG "alt=" Wkiom1nlutyt2dbwaaatyzkrhwe572.png "/>

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/A7/5E/wKioL1nluCejDkTGAAAw2Q-bTUY439.png "title=" 201706.PNG "alt=" Wkiol1nlucejdktgaaaw2q-btuy439.png "/>

This article is from the "Aimax" blog, make sure to keep this source http://aimax.blog.51cto.com/11610508/1973364

How to calculate xx year XX week in SQL Server which days