Ali Shah

asked on

# How to find number of weeks in previous month

Hi there,

I have got a requirement to find number of week in previous month. Can someone help please? I am using SQL Server 2012.

regards,

I have got a requirement to find number of week in previous month. Can someone help please? I am using SQL Server 2012.

regards,

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?

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

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

ASKER

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

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?

Mike

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;
```

Mike

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

Hi Mike,

thanks a lot and that is working like a charm.

kindest regards,

thanks a lot and that is working like a charm.

kindest regards,