Shulou(Shulou.com)06/01 Report--

SQL Server has a very powerful function: DATEPART ()

It can be calculated that 2017-10-17 belongs to this year.

Day: select datepart (dy,'2017-10-17')

Week: select datepart (wk,'2017-10-17')

Month: select datepart (mm,'2017-10-17')

Quarter: select datepart (qq,'2017-10-17')

What 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 arises: if we need to know what day the 25th week of 2017 is and how to write SQL

Analysis:

1. What is the date of the first day of week 25, 2017?

1.1 what day is 2017-1-1 on the first day of the first week of 2017

1.2 there are 7 days a week

2017-1-1 how many days should be added equal to the first day of week 25, 2017

So, the first day of week 25 = the number of days in the first week of 2017 + (25-2) * 7pr 25-2, minus week 1 and week 25

2. What day is the last day of the 25th week of 2017?

Last day of week 25, 2017 = Day 1 + 6 of week 25, 2017

At this point, we know that the key point is how to calculate the number of days in the first week of 2017, and the most important thing in calculating the number of days is to determine whether Sunday is the first or last day of the week.

January 1, 2017 happens to be Sunday.

If Sunday is the first day of the week

1st day of week 25, 2017 = January 1, 2017 + 7 days + (25-2) * 7 days

SQL statement: dateadd (day,7+ (25-2) * 7), the result is: 2017-6-18

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

If Sunday is the last day of the week

1st day of week 25, 2017 = January 1, 2017 + 1 day + (25-2) * 7 days

SQL statement: dateadd (day,1+ (25-2) * 7), the result is: 2017-6-12

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

Next, write the above calculation method as a SQL statement

Sunday is the first 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 @ firstweek=8-@dayofweek

Declare @ begin_date datetime

Declare @ end_date datetime

Set @ begin_date=dateadd (day, @ firstweek+ (@ weekofyear-2) * 7 recording 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 (9-@dayofweek) > 7

Begin

Set @ firstweek=9-@dayofweek-7

End

Else

Begin

Set @ firstweek=9-@dayofweek

End

Declare @ begin_date datetime

Declare @ end_date datetime

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

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

Then integrate the code into stored procedures, and the results are as follows:

The result verifies:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.