Getting Number of weeks in a Month from a Datetime Column

Question

I have a table called FcData and the data looks like:

Op_Date

2011-02-14 11:53:40.000
2011-02-17 16:02:19.000
2010-02-14 12:53:40.000
2010-02-17 14:02:19.000

I am looking to get the Number of weeks in That Month from Op_Date. So I am looking for output like:

 Op_Date                       Number of Weeks

    2011-02-14 11:53:40.000       5   
    2011-02-17 16:02:19.000       5
    2010-02-14 12:53:40.000       5
    2010-02-17 14:02:19.000       5

Solution 1

Use this to get the number of week for ONE specific date. Replace GetDate() by your date:

declare @dt date = cast(GetDate() as date);
declare @dtstart date =  DATEADD(day, -DATEPART(day, @dt) + 1, @dt);
declare @dtend date = dateadd(DAY, -1, DATEADD(MONTH, 1, @dtstart));

WITH dates AS (
     SELECT @dtstart ADate
     UNION ALL
     SELECT DATEADD(day, 1, t.ADate) 
       FROM dates t
      WHERE DATEADD(day, 1, t.ADate) <= @dtend
)
SELECT top 1 DatePart(WEEKDAY, ADate) weekday, COUNT(*) weeks
  FROM dates d
  group by DatePart(WEEKDAY, ADate)
  order by 2 desc

Explained: the CTE creates a result set with all dates for the month of the given date. Then we query the result set, grouping by week day and count the number of occurrences. The max number will give us how many weeks the month overlaps (premise: if the month has 5 Mondays, it will cover five weeks of the year).

Update

Now, if you have multiple dates, you should tweak accordingly, joining your query with the dates CTE.

Solution 2

This page has some good functions to figure out the last day of any given month: http://www.sql-server-helper.com/functions/get-last-day-of-month.aspx

Just wrap the output of that function with a DATEPART(wk, last_day_of_month) call. Combining it with an equivalent call for the 1st-day-of-week will let you get the number of weeks in that month.

Solution 3

Here is my take on it, might have missed something.

In Linq:

from u in TblUsers
let date = u.CreateDate.Value
let firstDay = new DateTime(date.Year, date.Month, 1)
let lastDay = firstDay.AddMonths(1)
where u.CreateDate.HasValue
select Math.Ceiling((lastDay - firstDay).TotalDays / 7)

And generated SQL:

-- Region Parameters
DECLARE @p0 Int = 1
DECLARE @p1 Int = 1
DECLARE @p2 Float = 7
-- EndRegion
SELECT CEILING(((CONVERT(Float,CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t3].[value], [t3].[value2]))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t3].[value], [t3].[value2]), [t3].[value]), [t3].[value2])) * 10000))) / 864000000000) / @p2) AS [value]
FROM (
    SELECT [t2].[createDate], [t2].[value], DATEADD(MONTH, @p1, [t2].[value]) AS [value2]
    FROM (
        SELECT [t1].[createDate], CONVERT(DATETIME, CONVERT(NCHAR(2), DATEPART(Month, [t1].[value])) + ('/' + (CONVERT(NCHAR(2), @p0) + ('/' + CONVERT(NCHAR(4), DATEPART(Year, [t1].[value]))))), 101) AS [value]
        FROM (
            SELECT [t0].[createDate], [t0].[createDate] AS [value]
            FROM [tblUser] AS [t0]
            ) AS [t1]
        ) AS [t2]
    ) AS [t3]
WHERE [t3].[createDate] IS NOT NULL