I'm not really good when it comes to database...
I'm wondering if it is possible to get the weeks of a certain month and year..
For example: 1 (January) = month and 2016 = year
Desired result will be:
week 1
week 2
week 3
week 4
week 5
This is what I have tried so far...
declare @date datetime = '01/01/2016'
select datepart(day, datediff(day, 0, @date) / 7 * 7) / 7 + 1
This only returns the total of the weeks which is 5.
declare @MonthStart datetime
-- Find first day of current month
set @MonthStart = dateadd(mm,datediff(mm,0,getdate()),0)
select
Week,
WeekStart = dateadd(dd,(Week-1)*7,@MonthStart)
from
( -- Week numbers
select Week = 1 union all select 2 union all
select 3 union all select 4 union all select 5
) a
where
-- Necessary to limit to 4 weeks for Feb in non-leap year
datepart(mm,dateadd(dd,(Week-1)*7,@MonthStart)) =
datepart(mm,@MonthStart)
Got the answer in the link: http://www.sqlservercentral.com/Forums/Topic1328013-391-1.aspx
Here is one way to approach this:
A month has a minimum of 29 or more days, and a max of 31 or less. Meaning there are almost always 5 weeks a month, with the exception of a non-leap year's feburary, and in those cases, 4 weeks a month.
You can refer to this to find out which years are "leap". Check for leap year
Hope this helps!
The following code will allow you to select a start and end date, and output one row per week, with numbered weeks, between those dates:
declare @start date = '1/1/2016'
declare @end date = '5/1/2016'
;with cte as (select @start date
, datename(month, @start) as month
union all
select dateadd(dd, 7, date)
, datename(month, dateadd(dd, 7, date))
from CTE
where date <= @end
)
select *, 'week '
+ cast(row_number() over (partition by month order by date) as varchar(1))
from CTE
order by date
