2

I have an input: @dateInput.

For example: @dateInput = 2013/10/02 (format: yyyy/MM/dd).

How can I get weeks (from Monday to Friday) in October like this by T-SQL

StartWeek                  EndWeek
2013-10-01 00:00:00.000    2013-10-05 00:00:00.000
2013-10-07 00:00:00.000    2013-10-12 00:00:00.000
2013-10-14 00:00:00.000    2013-10-19 00:00:00.000
2013-10-21 00:00:00.000    2013-10-26 00:00:00.000
2013-10-28 00:00:00.000    2013-10-31 00:00:00.000

Thanks all !

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Leo Le
  • 815
  • 3
  • 13
  • 33
  • Look here [Get first day of week in SQL Server](http://stackoverflow.com/questions/7168874/get-first-day-of-week-in-sql-server) and here [Is it possible to set start of week for T-SQL DATEDIFF function?](http://stackoverflow.com/questions/1101892/is-it-possible-to-set-start-of-week-for-t-sql-datediff-function) – Bogdan Bogdanov Nov 09 '13 at 17:20

1 Answers1

1

If you know roughly the year you can generate your pairs for year and when use them 

declare @input datetime
set @input = '20131002'

declare @monday datetime
set @monday='20121231'

declare @beginOfMonth datetime
declare @endOfMonth datetime

set @beginOfMonth = Substring(Convert(char,@input,102),1,8)+'01'
set @endOfMonth = DateAdd(month,1,@beginOfMonth)-1

declare @i int
set @i=1

create table #week
(StartDate datetime,
EndDate datetime)

while (@i <= 52)
begin
 insert into #week
 values (@monday, DateAdd(day,5,@monday))
 set @monday=DateAdd(week,1,@monday)
 set @i=@i+1
end

select * from #week
where StartDate between @beginOfMonth and @endOfMonth and EndDate between @beginOfMonth and    @endOfMonth 

drop table #week
Bridge
  • 29,818
  • 9
  • 60
  • 82
dblk1965
  • 132
  • 4
  • I've tried to run your script and I get last row EndDate result: 2013-11-02 00:00:00.000. My expectations is a date in October only (not in November). Can I do this ? – Leo Le Nov 10 '13 at 03:01
  • See amended code above. This breaks into real weeks Mon to Fri in your sample weeks are not real. Or you should provide more requirements. – dblk1965 Nov 10 '13 at 15:43