Q:

TSQL Calculate week number of the month

I want to calculate the week number of the month,I want to calculate the week number whether its odd or even week how can I get this in TSQL ? 
Thanks all!  

A:

This gives you the week of the date @dt within its month.   There is a 2nd column that uses a CASE statement over the expression, to show either "Odd" or "Even"
declare @dt datetime
set @dt = GETDATE()

select
    WhichWeekOfMonth = datepart(wk, @dt)
                     - datepart(wk,dateadd(m, DATEDIFF(M, 0, @dt), 0)) + 1,
    case when (datepart(wk, @dt)
            - datepart(wk,dateadd(m, DATEDIFF(M, 0, @dt), 0)) + 1) % 2 = 1
         then 'Odd' else 'Even' end

A:

It will give you week Number for Every month
declare @dates datetime
select @dates='2011-03-22'
SELECT datepart(dd,@dates), ceiling (cast(datepart(dd,@dates)as numeric(38,8))/7)  

A:

How about something readable, easily customizable, and predictable...
DECLARE @dayOfMonth AS INT;
SET @dayOfMonth = DATEPART(DAY, '3/14/2013');
SELECT CASE 
    WHEN @dayOfMonth < 8 THEN 1
    WHEN @dayOfMonth < 15 THEN 2
    WHEN @dayOfMonth < 22 THEN 3
    ELSE 4
END AS weekOfMonth;

