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

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.

Set first day of week and run the query again. Check this for details: http://msdn.microsoft.com/en-za/library/ms181598.aspx — danish, Dec 30 '14 at 07:01
@Danish looks like useful information. But I need to change query in this case. Can you suggest query by using `DATEFIRST` — Ankush Madankar, Dec 30 '14 at 07:11
If start date is Sunday then week_no should return as 4 for today — Deepak Mishra, Dec 30 '14 at 08:01
Added an answer based on: http://stackoverflow.com/questions/8642870/calculate-all-sundays-mondays-saturdays-between-two-days-in-sql-server. Please let me know if it works/not works. — Deepak Mishra, Dec 30 '14 at 10:48
is the first day of the month and the next 6 days always considered week 1 of the month ? — t-clausen.dk, Dec 30 '14 at 11:43

Sarath Subramanian · Accepted Answer · 2014-12-31T06:02:06.553

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

edited Dec 31 '14 at 06:02

answered Dec 30 '14 at 09:28

Sarath Subramanian

20,027
11
82
86

Run same query with input parameter `@FROMDATE` with `getdate()+2`, it gives output `2015_JANUARY_WEEK_NO_0` which is not correct. – Ankush Madankar Dec 30 '14 at 09:32
What should be result for that date? @Ankush Madankar – Sarath Subramanian Dec 30 '14 at 09:35
`2015_JANUARY_WEEK_NO_1` – Ankush Madankar Dec 30 '14 at 09:38
I have updated the answer. Please check @Ankush Madankar – Sarath Subramanian Dec 30 '14 at 09:44
Check with `getdate() + 8` it gives `2015_JANUARY_WEEK_NO_1` but week is second for that day (2015-01-07 Wend) i.e `2015_JANUARY_WEEK_NO_2` – Ankush Madankar Dec 30 '14 at 09:55
Thats because 2015-01-07 Wend is the first Wend after first Tuesday of that month. And week2 will be the week for the days from 2nd Tuesday right? @Ankush Madankar – Sarath Subramanian Dec 30 '14 at 12:07
I have added the UPDATE. Please check @Ankush Madankar – Sarath Subramanian Dec 30 '14 at 12:46
Working? @Ankush Madankar – Sarath Subramanian Dec 31 '14 at 05:15
Not working for `getdate()` as input parameter, output => `2014_DECEMBER_WEEK_NO_51` – Ankush Madankar Dec 31 '14 at 05:35
Sorry for the interuption. I have updated in UPDATE. It will work now. Please check @Ankush Madankar – Sarath Subramanian Dec 31 '14 at 06:02
Working? @Ankush Madankar – Sarath Subramanian Dec 31 '14 at 08:32

score 1 · Answer 2 · answered Dec 30 '14 at 10:38

1

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)

answered Dec 30 '14 at 10:38

Deepak Mishra

2,984
1
26
32

This wont work for 2 month i.e. `@StartDate = '2014-12-01' and @EndDate = '2015-01-01'` (`getdate() + 2`), gives result `2015_JANUARY_WEEK_NO_5` – Ankush Madankar Dec 30 '14 at 10:50
You should give start date as the first date of the month as you want the answer as week_no_1. Anyway, what should be the answer according to you? – Deepak Mishra Dec 30 '14 at 10:55
I don't know exact answer. I need this somewhere in report which shows weekly and monthly count of document. I am able to do for monthly and unable for weekly. Thanks for your response. Both sarath and you. Now I think I need to find work-around! – Ankush Madankar Dec 30 '14 at 11:00
What should be the 'WEEK_NO' for 1-DEC-2014? – Deepak Mishra Dec 30 '14 at 11:12

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

2 Answers2

Linked