CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlsql-server-2008
Peter
Peter

Reputation: 651

Getting Number of weeks in a Month from a Datetime Column

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

Upvotes: 2

Views: 15953

Answers (7)

Val
Val

Reputation: 539

Here how you can get accurate amount of weeks:

DECLARE @date DATETIME
SET @date = GETDATE()
SELECT ROUND(cast(datediff(day, dateadd(day, 1-day(@date), @date), dateadd(month, 1, dateadd(day, 1-day(@date), @date))) AS FLOAT) / 7, 2)

With this code for Sep 2014 you'll get 4.29 which is actually true since there're 4 full weeks and 2 more days.

Upvotes: 0

Vishal Shah
Vishal Shah

Reputation: 1

You can get number of weeks per month using the following method.

Datepart(WEEK,
         DATEADD(DAY,
                 -1,
                 DATEADD(MONTH,
                         1,
                         DATEADD(DAY,
                                 1 - DAY(GETDATE()),
                                 GETDATE())))
         -
         DATEADD(DAY,
                 1 - DAY(GETDATE()),
                 GETDATE())
         +1
         )

Upvotes: 0

Andriy M
Andriy M

Reputation: 77657

There may be various approaches to implementing the idea suggested by @Marc B. Here's one, where no UDFs are used but the first and the last days of month are calculated directly:

WITH SampleData AS (
  SELECT CAST('20110214' AS datetime) AS Op_Date
  UNION ALL SELECT '20110217'
  UNION ALL SELECT '20100214'
  UNION ALL SELECT '20100217'
  UNION ALL SELECT '20090214'
  UNION ALL SELECT '20090217'
),
MonthStarts AS (
  SELECT
    Op_Date,
    MonthStart = DATEADD(DAY, 1 - DAY(Op_Date), Op_Date)
    /* alternatively: DATEADD(MONTH, DATEDIFF(MONTH, 0, Op_Date), 0) */
  FROM FcData
),
Months AS (
  SELECT
    Op_Date,
    MonthStart,
    MonthEnd = DATEADD(DAY, -1, DATEADD(MONTH, 1, MonthStart))
  FROM FcData
)
Weeks AS (
  SELECT
    Op_Date,
    StartWeek = DATEPART(WEEK, MonthStart),
    EndWeek   = DATEPART(WEEK, MonthEnd)
  FROM MonthStarts
)
SELECT
  Op_Date,
  NumberOfWeeks = EndWeek - StartWeek + 1
FROM Weeks

All calculations could be done in one SELECT, but I chose to split them into steps and place every step in a separate CTE so it could be seen better how the end result was obtained.

Upvotes: 0

Magnus
Magnus

Reputation: 46919

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

Upvotes: 1

Adriano Carneiro
Adriano Carneiro

Reputation: 58595

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.

Upvotes: 1

Marc B
Marc B

Reputation: 360602

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.

Upvotes: 2

endyourif
endyourif

Reputation: 2202

According to this MSDN article: http://msdn.microsoft.com/en-us/library/ms174420.aspx you can only get the current week in the year, not what that month returns.

Upvotes: 0

Related Questions