SQL Blog by Rahul Sahay

I have a total experience of 6 years primarily in databases (T-SQL and Performance Tuning). I have worked on different versions starting with SQL Server 2000 to SQL Server 2008 R2 for companies like Accenture and Airtel. I have completed Microsoft certification in MCTS (SQL SERVER). I did my MCA (Masters Of Computer Application) degree from Bangalore and have a BCA (Bachelor Of Computer Applications) from Indore.

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.