I'm currently trying to write a stored procedure that can compute the biweekly periods when a date is passed in as a parameter. The business logic: the first Monday of the year is first day of the biweekly period. For example in 2010: period period_start period_end 1 2010-01-04 2010-01-17 2 2010-01-18 2010-01-31 3 2010-02-01 .... 26 2010-12-20 2011-01-02 I'm not too strong in T-SQL. Any help is appreciated. I'm trying to find out how to get the first monday of the year. Thanks
Try this to show you how to get the first monday of the year. It's been hacked together in a few minutes before heading for home, so doesn't provide your complete result set above, but it will show you how to generate that Monday! declare @Year int select @Year = 2011 -- Change this as appropriate declare @FirstMonday datetime DECLARE @FirstDay datetime SELECT @FirstDay = convert(datetime, convert(char(4), @Year) + '-01-01') select @FirstMonday = dateadd(day, CASE WHEN datepart(weekday, @FirstDay)
About first monday try the following: DECLARE @date DATE = '20100101'; --this will give the first monday including @date. SELECT DATEADD(WEEK, 1 + DATEDIFF(WEEK, 0, DATEADD(DAY, -1, @date)), 0); But regarding the rest of question, you need to provide us with more information.