如何根据DATEFIRST找到一个日期的周数(How do I find week number o

如何找到一个的周数date ，如果起始日期是星期二和结束日期是星期一？

下面是示例标准

      Date                          Output  
     ......                       ..........            
 01-Dec-2014 (Monday)   - 2014_DECEMBER_WEEK_NO_1 
 04-Dec-2014 (Thursday) - 2014_DECEMBER_WEEK_NO_1 
 29-Dec-2014 (Monday)   - 2014_DECEMBER_WEEK_NO_4 
 30-Dec-2014 (Tuesday)  - 2014_DECEMBER_WEEK_NO_5 
 31-Dec-2014 (Tuesday)  - 2014_DECEMBER_WEEK_NO_5

我想下面的查询：

select CAST(UPPER(DATENAME(YEAR, @FROMDATE)) AS VARCHAR(20))
+'_'+CAST(UPPER(DATENAME(MONTH, @FROMDATE)) AS VARCHAR(20))
+'_WEEK_NO_'+CAST((DAY(@FROMDATE) 
+ (DATEPART(DW, DATEADD (MONTH, DATEDIFF (MONTH, 0, @FROMDATE), 0))-1) -1)/7 + 1 AS VARCHAR(10))

这个工作很好，如果开始日是星期天和结束日是星期六。

Answer 1:

你的第一天设置为Tuesday

SET DATEFIRST 2

执行以下查询

DECLARE @FROMDATE DATE='31-Dec-2014'

SELECT CAST(UPPER(DATENAME(YEAR, @FROMDATE)) AS VARCHAR(20))
+'_'+CAST(UPPER(DATENAME(MONTH, @FROMDATE)) AS VARCHAR(20))
+'_WEEK_NO_'+REPLACE(CAST((DAY(@FROMDATE) 
+ (DATEPART(DW, DATEADD (MONTH, DATEDIFF (MONTH, 0, @FROMDATE), 0))-1) -1)/7  AS VARCHAR(10)),'0','1')

UPDATE

SET DATEFIRST 2

DECLARE @FROMDATE DATE='12-JAN-2015'
DECLARE @ALLDATE DATE=DATEADD(month, DATEDIFF(month, 0, @FROMDATE), 0)
DECLARE @FIRSTDATE DATE


; WITH  CTE as
 (
     SELECT 1 RNO,CAST(@ALLDATE AS DATE) as DATES 
     UNION ALL
     SELECT RNO+1, DATEADD(DAY,1,DATES )
     FROM    CTE
     WHERE   DATES < DATEADD(MONTH,1,@ALLDATE)
 )
 SELECT TOP 1 @FIRSTDATE =   DATES 
 FROM    CTE 
 WHERE DATEPART(W,DATES)=1


SELECT CAST(UPPER(DATENAME(YEAR, @FROMDATE)) AS VARCHAR(20))
+'_'+CAST(UPPER(DATENAME(MONTH, @FROMDATE)) AS VARCHAR(20))
+'_WEEK_NO_'+CAST((DATEDIFF(DAY,@FIRSTDATE,@FROMDATE)/7)+1  AS VARCHAR(10))

Answer 2:

set datefirst 2 

DECLARE  @StartDate DATE = '2014-12-01',
         @EndDate   DATE = '2014-12-31',
         @DayCount int 


SELECT @DayCount = count(* )
FROM   (SELECT TOP ( datediff(DAY,@StartDate,@EndDate) + 1 )
                        [Date] = dateadd(DAY,ROW_NUMBER()
                  OVER(ORDER BY c1.name),
                  DATEADD(DD,-1,@StartDate))
        FROM   [master].[dbo].[spt_values] c1 ) x
WHERE  datepart(dw,[Date]) = 1;


select CAST(UPPER(DATENAME(YEAR, @EndDate)) AS VARCHAR(20))
+'_'+CAST(UPPER(DATENAME(MONTH, @EndDate)) AS VARCHAR(20))
+'_WEEK_NO_'+ Cast(@DayCount as varchar)

文章来源: How do I find week number of a date according to DATEFIRST