Sql查询获取周数从星期一开始的周数 [英] Sql query to get week number where week starts with monday

问题描述

Hi Team，



i尝试了很多方法来获得低于要求的周数，但没有成功。



要求：

一周的第一天从：星期一开始

所以它必须得到如下的周数。

28thDec2015 - 3rdJan2016 =>第1周

4thJan2016 - 10thJan2016 =>第2周

soo ..

基于上面的给定输入它给出了适当的周数。



我尝试过：

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:

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

推荐答案

你非常接近答案。要实现所需的自定义逻辑，可以创建存储过程或函数。以下面的tsql为例。基本上使用DateDiff SQL函数，将它传递给wk一周，然后开始和结束日期，SQL服务器将为您完成所有工作。

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.

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 )

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

这篇关于Sql查询获取周数从星期一开始的周数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！