SQL BLOG: Calculating number of week along the days completed in that week for a given input date

Below code will display the week started along with the number of days completed in that week. For deriving this have used the modulo function.

Week-4 for this code starts from 22nd till the last day of the month, that's why week-4 remaining days are calculated by dividing with 21



/* CODE STARTS BELOW: */

/* FOR A SELECTIVE DATE, WILL GIVE THE WEEK COUNT ALONG WITH THE DAYS COMPLETED IN THAT WEEK*/

declare @tdate datetime
set @tdate = '2012-02-29'
SELECT @tdate as Todays_Date,
CASE WHEN ((DATEPART(DD,@tdate))/7) = 0 THEN 'WK-1'
     WHEN ((DATEPART(DD,@tdate))/7) = 1 THEN 'WK-2'
     WHEN ((DATEPART(DD,@tdate))/7) = 2 THEN 'WK-3'
     ELSE 'WK-4'
     END WEEK_COUNT
,CASE WHEN ((DATEPART(DD,@tdate))/7) = 0 THEN ((DATEPART(DD,@tdate))%7)
     WHEN ((DATEPART(DD,@tdate))/7) = 1 THEN ((DATEPART(DD,@tdate))%7)
     WHEN ((DATEPART(DD,@tdate))/7) = 2 THEN ((DATEPART(DD,@tdate))%7)
     ELSE ((DATEPART(DD,@tdate))%21)
     END Number_Of_Days_In_Week    

Sample Output:
Todays Date                          Week_Count      Number_Of_Days_In_Week
2012-02-29 00:00:00.000         WK-4                               8

Pls Note:    many  more conditions and logics can be applied to encounter different scenarios, which i will be coming up in my future scripts.