Home Forums SQL Server 2005 T-SQL (SS2K5) How to get week of a mont with input parameter date. RE: How to get week of a mont with input parameter date.

  • Easy enough:

    DECLARE @InputDate DATE = GETDATE();

    SELECT DATEADD(month, DATEDIFF(MONTH, 0, @InputDate), 0) AS FirstDayOfMonth,

    DATEADD(week, DATEDIFF(week, 0, DATEADD(month, DATEDIFF(MONTH, 0, @InputDate), 0)), 0) - 1 AS FirstDayOfWeek,

    DATEADD(week, DATEDIFF(week, 0, DATEADD(month, DATEDIFF(MONTH, 0, @InputDate), 0)), 0) AS SecondDayOfWeek,

    DATEADD(week, DATEDIFF(week, 0, DATEADD(month, DATEDIFF(MONTH, 0, @InputDate), 0)), 0) + 1 AS ThirdDayOfWeek,

    DATEADD(week, DATEDIFF(week, 0, DATEADD(month, DATEDIFF(MONTH, 0, @InputDate), 0)), 0) + 2 AS FourthDayOfWeek,

    DATEADD(week, DATEDIFF(week, 0, DATEADD(month, DATEDIFF(MONTH, 0, @InputDate), 0)), 0) + 3 AS FifthDayOfWeek,

    DATEADD(week, DATEDIFF(week, 0, DATEADD(month, DATEDIFF(MONTH, 0, @InputDate), 0)), 0) + 4 AS SixthDayOfWeek,

    DATEADD(week, DATEDIFF(week, 0, DATEADD(month, DATEDIFF(MONTH, 0, @InputDate), 0)), 0) + 5 AS SeventhDayOfWeek;

    The nested DateAdd, DateDiff method allows you to get the first X (time-unit) of any given DateTime value. If you use Days as your unit, you get the very beginning of the day (midnight at the end of the prior day, to be precise). If you use Weeks as your unit, you get the first day of the week. And so on.

    This one has to be done in two stages:

    First, get the first day of the month. I put that in its own column, just so you could see how it's done. You can remove that column from the query if you don't want it.

    Second, get the first day of the week that the first day of the month is in. So it nests the month calculation inside a week calculation.

    The math at the end of each row is based on Monday being the first day of the week on the server I ran this on. You'll need to confirm that and may need to change the "-1", "+1" through "+5" if the first day of the week is defined as Sunday or whatever on your server.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon