How do I find week number of a date according to D

Question

How to find week number of a date if start day is Tuesday and end day is Monday?

Here is the example criteria

      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

I tried following query:

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

This working fine if start day is Sunday and end day is Saturday.

Answer 1

Set your first day to Tuesday

SET DATEFIRST 2

Execute the following query

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)