如何获得周数SQL Server 2014

How to get Week number based on current date in SQL Server 2014?

如何根据SQL Server 2014中的当前日期获取周数？

Please note always year should starts from 1st February and year end will be 31st Jan.

请注意，年份应从2月1日开始，年末将从1月31日开始。

Week Starts from Sunday to Saturday.

周从周日到周六开始。

4 个解决方案

#1

You can get the number of weeks since Feb 1 and then use that to calculate weeks:

您可以获得自2月1日以来的周数，然后使用它来计算周数：

select (case when month(getdate()) < 2
             then datediff(week,
                           datefromparts(year(getdate()) - 1, 2, 1),
                           getdate()
                          )
             else datediff(week,
                           datefromparts(year(getdate()), 2, 1),
                           getdate()
                          )
         end)

Note: datefromparts() was introduced in SQL Server 2012. You can do similar things with strings and conversion to dates.

注意：datefromparts（）是在SQL Server 2012中引入的。您可以使用字符串和转换为日期来执行类似的操作。

#2

Try this

尝试这个

SELECT CASE WHEN MONTH(GETDATE()) < 2 THEN (DATEPART(DAYOFYEAR,DATEADD(M,11,GETDATE())))/7 
            ELSE ((DATEPART(DAYOFYEAR,GETDATE())-31)/7)+1 
        END

#3

  declare @DATE_INSERT date='02-01-2014'
 select DATEPART(WK,@DATE_INSERT) - DATEPART(WK,DATEADD(DAY,1,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DATE_INSERT),0)))) + 1

#4

Depends how you define week number, but you can try this:

取决于您如何定义周数，但您可以尝试这样做：

SELECT DATEPART( wk, DATEADD('mm',-1,getdate()))

#1