How to get week number of the month from the date in sql server 2008

Question

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

User · Answer

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

User · Answer

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

User · Answer

Logic here works as well 4 3 weeks in every month  Take that from the DATEPART WEEK  on every month but January  Just another way of looking at things  This would also account for months where there is a 5th week  DECLARE  date VARCHAR 10  SET  date    7 27 2019   SELECT CEILING DATEPART WEEK  date -  DATEPART MONTH  date -1  4 3333    Week of Month

User · Answer

Here is the tried and tested solution for this query in any situation - like if 1st of the month is on Friday   then also this will work -   select  DATEPART wk  date given -DATEPART wk dateadd d 1-day  date given   date given    1   above are some solutions which will fail if the month s first date is on Friday   then 4th will be 2nd week of the month

User · Answer

WeekMonth   CASE WHEN  DATEPART day TestDate  - datepart dw TestDate   gt   22 THEN  5                    WHEN  DATEPART day TestDate  - datepart dw TestDate   gt   15 THEN  4                    WHEN  DATEPART day TestDate  - datepart dw TestDate   gt   8 THEN  3                   WHEN  DATEPART day TestDate  - datepart dw TestDate   gt   1 THEN  2                    ELSE  1               END

User · Answer

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

User · Answer

floor  day  DateValue -1  7  1

User · Answer

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 day 0 dateadd month      datediff month 0  date  0   7  0   date-1    1   I recieved an email from Gerald  He pointed out a flaw in the second method  This should be fixed now

User · Answer

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

User · Answer

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   lt  8 THEN  1     ELSE CASE WHEN DATEPART day yourdate   lt  15 then  2       ELSE CASE WHEN  DATEPART day yourdate   lt  22 then  3         ELSE CASE WHEN  DATEPART day yourdate   lt  29 then  4               ELSE  5        END     END   END END

User · Answer

declare  end date datetime    2019-02-28   select datepart week   end date  - datepart week  convert datetime  substring convert nvarchar  convert datetime   end date   127   1  8     01      1  Week of Month

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

There is an inbuilt option to get the week number of the year    select datepart week getdate

User · Answer

Solution   declare  dt datetime  2018-03-31 05 16 00 000  IF  Select  DatePart DAY  dt  7   gt 0 Select   DatePart DAY  dt  7   1 ELSE Select   DatePart DAY  dt  7

User · Answer

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

User · Answer

select  DateCreated  DATEDIFF WEEK   DateCreated  GETDATE

User · Answer

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

User · Answer

Try Below Code   declare  dt datetime  2018-03-15 05 16 00 000  IF  Select  DatePart DAY  dt  7   gt 0   Select   DatePart DAY  dt  7   1 ELSE   Select   DatePart DAY  dt  7

[sql-server-2008] How to get week number of the month from the date in sql server 2008

Examples related to sql-server-2008

Examples related to tsql