We're after the week number of the year here, not the week number of the month 🙂

DECLARE @dt DATETIME;

SET @dt = '2010-07-19';

SELECT DATEPART(WEEK, DAY(@dt)); -- 3

SELECT DATEPART(WEEK, @dt); -- 30

The second example shows that 19th July 2010 is in week 30 of this year (with my current system settings).

It's unclear to me what your code is showing. 19th July isn't in the third week of July on my calendar (July started on a Thursday, and weeks run from Monday for me).

It is in the third week of July if you consider the first 7 days of any month to be the 'first week' and so on - is that the purpose of your function? If so, why does it return 4 for 20th July 2010? If I needed to know this sort of information, I would probably just use something like SELECT DAY(@dt) / 7 + 1; there's no need for the DATEPART at all.

Paul