First Monday of the Year in SQL Server
Here's the example from the link ngruson posted (http://sqlbump.blogspot.nl/2010/01/first-monday-of-year.html):
DECLARE @Date datetime
DECLARE @Year int = 2012
SET @Date = DATEADD(YEAR, @Year - 1900, 0)
SELECT DATEADD(DAY, (@@DATEFIRST - DATEPART(WEEKDAY, @Date) +
(8 - @@DATEFIRST) * 2) % 7, @Date)
The above returns:
2012-01-02 00:00:00.000
Here,
1) DATEPART function for finding day of week.
Here 1=sunday,2=monday,..,7=saturday.
2) first find out is 2(monday) in case when
condition then get same date,
3) if is sunday(1) then add 1 day and get next
day date is for monday,
4) if is not 2(monday) and greater then 2 then find
difference between lastday(7) and add 2 days ,
so we reach to monday date.
Here are you change year in @year variable. Ex:
DECLARE @Date datetime
DECLARE @Year int = 2012
set @Date= convert(varchar(4),@Year) + '-01-01'
select @Date,(case when DATEPART(DW,@Date)=2 then @Date
when DATEPART(DW,@Date)=1 then DATEADD(day,1,@Date)
else DATEADD(day,7-DATEPART(DW,@Date)+2,@Date)
end) as MondayDateis
Elegant it is not ;)
DECLARE @year DATETIME = '01 jan 2012'
SELECT [Day]
FROM
(
SELECT @year [Day] UNION ALL
SELECT DATEADD(DAY, 1, @year) UNION ALL
SELECT DATEADD(DAY, 2, @year) UNION ALL
SELECT DATEADD(DAY, 3, @year) UNION ALL
SELECT DATEADD(DAY, 4, @year) UNION ALL
SELECT DATEADD(DAY, 5, @year) UNION ALL
SELECT DATEADD(DAY, 6, @year)
) x
WHERE
DATENAME(DW, [Day]) = 'Monday'