How to find number of weeks in previous month

SELECT DATEPART(wk, dateadd("M",-1,GETDATE()))

What do you mean the "number of week in previous month"?

There are 4 weeks in the previous month. There are always 4 weeks in the previous month. Some months have an extra couple of days.

Give me some example inputs and outputs?

Or do you want the week number?

3 x 7 = 21
28 - 21 = 7 (Feb could be 28)

Now if the first day is wed, then there will be 4 leading days (wed, thr, fri, sat) not forming a full week and 3 trailing days at the end of the month (again not forming a full day).

The best way is a function call (it will be okay if you are not working with large number of data where function calls are not as efficient).

What day, are you considering the start day for the week?

Mike

Hi everyone thanks for your replies.

The requirement is to get calculate percentage of the expected timecards to be completed and the actual timecards completed in the previous month. The timecards are completed on weekly basis.

For the week seperated between two months belongs to the month when it was started.

for example the week commencing on July 29 - 02 August will be counted as July's week.

at the moment i am trying to do in the function like this but i suppose this will need changing in the year 2014.

ALTER FUNCTION [dbo].[ufn_GetWeeks]
(
      -- Add the parameters for the function here
      @Date datetime
)
RETURNS int
AS
BEGIN
      DECLARE @TotW float
      
      declare @Mname char  

set @Mname=  datename(month, dateadd(month, -1, @Date))

      -- Return the result of the function
      If (@Mname = 'June')
            Set @TotW = 4

            If (@Mname = 'July')
            Set @TotW = 5

            If (@Mname = 'August')
            Set @TotW = 4
            If (@Mname = 'September')
            Set @TotW = 5
            If (@Mname = 'October')
            Set @TotW = 4
            If (@Mname = 'November')
            Set @TotW = 4
            If (@Mname = 'December')
            Set @TotW = 5

      

      return @Totw

END

This gives you the weeks in current month:
It works regardless what year it is.

Do you need to supply date criteria or current date (current month) is acceptable?

CREATE FUNCTION fnFindNoOfWeeks (@DateVar varchar(30))
RETURNS int
-- WITH SCHEMABINDING AS
BEGIN
	DECLARE @N AS int -- store number of weeks
	DECLARE @firstMonday as varchar(30) -- store first monday of the month
	DECLARE @ForthMonday as varchar(30) -- store forth monday of the month
	DECLARE @FifthMonday as varchar(30) -- store forth monday of the month
	
	set @firstMonday = DATEADD(WEEK, DATEDIFF(WEEK, 0, -- find first monday of the month
            DATEADD(DAY, 6 - DATEPART(DAY, GETDATE()), GETDATE())), 0)
            
	set @ForthMonday = dateadd(DAY, 21, @firstMonday)		-- add 6+7+7 days to get to the end of third week 
															-- add 1 day to get to the monday of forth week
	                                                
	set @FifthMonday = dateadd(DAY,(6+1), @ForthMonday)     -- add 6 days to get to the end of forth week
															-- add 1 day to get to monday of fifth week  
	                                                  
	set @N = 3 -- start with 3 weeks in a month                                                                                                
	if datepart(month, @firstMonday)=datepart(month, @ForthMonday) -- if @ForthMonday the same as @firstMonday 
		set @N = @N +1

		
	if datepart(month, @firstMonday)=datepart(month, @FifthMonday) -- if @FifthMonday the same as @firstMonday 
		set @N = @N +1
	

  RETURN @N
END;

Select allOpen in new window

Mike

Hi Mike,

thanks a lot and that is working like a charm.

kindest regards,