How to get week number of the month from the date

问题:

In SQL Statement in microsoft sql server, there is a built-in function to get week number but it is the week of the year.

Select DatePart(week, '2012/11/30') // **returns 48**

The returned value 48 is the week number of the year.

Instead of 48, I want to get 1, 2, 3 or 4 (week number of the month). I think the week number of the month can be achieved by modules with Month Number of this week. For e.g.

Select DATEPART(week, '2012/11/30')%MONTH('2012/11/30')

But I want to know is there other built-in functions to get WeekNumber of the month in MS SQL SERVER.

回答1:

Here are 2 different ways, both are assuming the week starts on monday

If you want weeks to be whole, so they belong to the month in which they start: So saturday 2012-09-01 and sunday 2012-09-02 is week 4 and monday 2012-09-03 is week 1 use this:

declare @date datetime = '2012-09-01'
select datepart(day, datediff(day, 0, @date)/7 * 7)/7 + 1

If your weeks cut on monthchange so saturday 2012-09-01 and sunday 2012-09-02 is week 1 and monday 2012-09-03 is week 2 use this:

declare @date datetime = '2012-09-01'
select datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, @date), 0)), 0), @date - 1) + 1

回答2:

DECLARE @DATE DATETIME
SET @DATE = '2013-08-04'

SELECT DATEPART(WEEK, @DATE)  -
    DATEPART(WEEK, DATEADD(MM, DATEDIFF(MM,0,@DATE), 0))+ 1 AS WEEK_OF_MONTH

回答3:

No built-in function. It depends what you mean by week of month. You might mean whether it's in the first 7 days (week 1), the second 7 days (week 2), etc. In that case it would just be

(DATEPART(day,@Date)-1)/7 + 1

If you want to use the same week numbering as is used with DATEPART(week,), you could use the difference between the week numbers of the first of the month and the date in question (+1):

(DATEPART(week,@Date)- DATEPART(week,DATEADD(m, DATEDIFF(m, 0, @Date), 0))) + 1

Or, you might need something else, depending on what you mean by the week number.

回答4:

Just look at the date and see what range it falls in.

Range 1-7 is the 1st week, Range 8-14 is the 2nd week, etc.

SELECT 
CASE WHEN DATEPART(day,yourdate) < 8 THEN '1' 
  ELSE CASE WHEN DATEPART(day,yourdate) < 15 then '2' 
    ELSE CASE WHEN  DATEPART(day,yourdate) < 22 then '3' 
      ELSE CASE WHEN  DATEPART(day,yourdate) < 29 then '4'     
        ELSE '5'
      END
    END
  END
END

回答5:

There is no inbuilt function to get you the week number. I dont think dividing will help you anyway as the number of weeks in a month is not constant.

http://msdn.microsoft.com/en-us/library/bb675168.aspx

I guess you can divide the number(48) by 4 and take the modules of the same and project that as the week number of that month, by adding one to the result.

回答6:

Here's a suggestion for getting the first and last days of the week for a month:

-- Build a temp table with all the dates of the month 
drop table #tmp_datesforMonth 
go

declare @begDate datetime
declare @endDate datetime

set @begDate = '6/1/13'
set @endDate = '6/30/13';

WITH N(n) AS  
(   SELECT 0  
        UNION ALL 
    SELECT n+1 
    FROM N 
    WHERE n <= datepart(dd,@enddate)
)
SELECT      DATEADD(dd,n,@BegDate) as dDate 
into #tmp_datesforMonth
FROM        N
WHERE       MONTH(DATEADD(dd,n,@BegDate)) = MONTH(@BegDate)

--- pull results showing the weeks' dates and the week # for the month (not the week # for the current month) 

select  MIN(dDate) as BegOfWeek
, MAX(dDate) as EndOfWeek 
, datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, dDate), 0)), 0), dDate) as WeekNumForMonth 
from #tmp_datesforMonth
group by datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, dDate), 0)), 0), dDate) 
order by 3, 1

回答7:

Check this out... its working fine.

declare @date as datetime = '2014-03-10'
select DATEPART(week,@date) - DATEPART(week,cast(cast(year(@date) as varchar(4))+'-' + cast(month(@date) as varchar(2)) + '-01' as datetime))+1

回答8:

A dirty but easy one liner using Dense_Rank function. Performance WILL suffer, but effective none the less.

DENSE_RANK()over(Partition by Month(yourdate),Year(yourdate) Order by Datepart(week,yourdate) asc) as Week

回答9:

Similar to the second solution, less code:

declare @date datetime = '2014-03-31'
SELECT DATEDIFF(week,0,@date) - (DATEDIFF(week,0,DATEADD(dd, -DAY(@date)+1, @date))-1)

回答10:

Here is the query that brings the week number on whatever the startday and endday of the week it may be.

SET DATEFIRST 2    

DECLARE @FROMDATE DATE='12-JAN-2015'
-- Get the first day of month
DECLARE @ALLDATE DATE=DATEADD(month, DATEDIFF(month, 0, @FROMDATE), 0)
DECLARE @FIRSTDATE DATE


;WITH  CTE as
(
     -- Get all dates in that month
     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)
)
-- Retrieves the first day of week, ie, if first day of week is Tuesday, it selects first Tuesday 
SELECT TOP 1 @FIRSTDATE =   DATES 
FROM    CTE 
WHERE DATEPART(W,DATES)=1

SELECT (DATEDIFF(DAY,@FIRSTDATE,@FROMDATE)/7)+1 WEEKNO

For more information I have answered for the below question. Can check that.

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

回答11:

floor((day(@DateValue)-1)/7)+1

回答12:

Here you go....

Im using the code below..

DATEPART(WK,@DATE_INSERT) - DATEPART(WK,DATEADD(DAY,1,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DATE_INSERT),0)))) + 1

回答13:

Try Below Code:

declare @dt datetime='2018-03-15 05:16:00.000'
IF (Select (DatePart(DAY,@dt)%7))>0
  Select  (DatePart(DAY,@dt)/7) +1
ELSE
  Select  (DatePart(DAY,@dt)/7)

回答14:

You can simply get week number by getting minimum week number of month and deduct it from week number. Suppose you have a table with dates

select
    emp_id, dt , datepart(wk,dt) - (select min(datepart(wk,dt))
from
    workdates ) + 1 from workdates

回答15:

Solution:

declare @dt datetime='2018-03-31 05:16:00.000'
IF (Select (DatePart(DAY,@dt)%7))>0
Select  (DatePart(DAY,@dt)/7) +1
ELSE
Select  (DatePart(DAY,@dt)/7)

回答16:

Code is below:

set datefirst 7
declare @dt datetime='29/04/2016 00:00:00'
select (day(@dt)+datepart(WEEKDAY,dateadd(d,-day(@dt),@dt+1)))/7