As was stated earlier, a calendar table would be your biggest friend here.

If you absolutely must do it just using some date functions, the following sort of code would do it. It's extremely ugly, and I'm sure there are much more elegant solutions. It was just the first solution my brain suggested, and at least provides some hint of the sort of logic you could use.

Still, I'd much rather use a calendar table than something like this 🙂

DECLARE @SomeDate DATE

SET @SomeDate='04/3/2014'

SELECT

StraddleMonth=CASE WHEN datename(month,dateadd(day,(1-datepart(WEEKDAY,@SomeDate)),@SomeDate))=datename(month,dateadd(day,(7-datepart(WEEKDAY,@SomeDate)),@SomeDate))

THEN datename(month,dateadd(day,(1-datepart(WEEKDAY,@SomeDate)),@SomeDate))

ELSE datename(month,dateadd(day,(1-datepart(WEEKDAY,@SomeDate)),@SomeDate))+'/'+datename(month,dateadd(day,(7-datepart(WEEKDAY,@SomeDate)),@SomeDate))

END,

WeekNumber=DATEPART(Week,@SomeDate)