Number of weeks in a month

How can i write a Query to find out how many ws are there in a month,
I tried Datediff(wk, startofMonth ,EndOfMont)
this is not giving correct output
for example : Feb 1998 = 4ws
july 2006 = 6 ws
lara--
-- This will calculate the number of FULL ws
-- (Sunday thru Saturday) in a month.
--
DECLARE @.FirstOfMonth datetime
DECLARE @.FirstSunday datetime
DECLARE @.LastOfMonth datetime
DECLARE @.WsInMonth int
SELECT @.FirstOfMonth = '05/01/2006' -- <<<<<<<<< Change this as
needed.
-- Get the last date of the month by adding 1 to the
-- month and then subtracting 1 day.
SELECT @.LastOfMonth = DATEADD(m, 1, @.FirstOfMonth)
SELECT @.LastOfMonth = DATEADD(d, -1, @.LastOfMonth)
-- Find the first Sunday in the month
SELECT @.FirstSunday = CASE DatePart(dw, @.FirstOfMonth)
WHEN '1' THEN @.FirstOfMonth
WHEN '2' THEN DATEADD(d, 6, @.FirstOfMonth)
WHEN '3' THEN DATEADD(d, 5, @.FirstOfMonth)
WHEN '4' THEN DATEADD(d, 4, @.FirstOfMonth)
WHEN '5' THEN DATEADD(d, 3, @.FirstOfMonth)
WHEN '6' THEN DATEADD(d, 2, @.FirstOfMonth)
WHEN '7' THEN DATEADD(d, 1, @.FirstOfMonth)
END
-- Calculate the number of ws between the first Sunday
-- and the last date of the month.
SELECT @.WsInMonth = (DATEDIFF(d, @.FirstSunday, @.LastOfMonth)+1) / 7
SELECT @.WsInMonth
"aa" <dearme23in@.yahoo.com> wrote in message
news:OIUIbCWeGHA.1272@.TK2MSFTNGP03.phx.gbl...
> How can i write a Query to find out how many ws are there in a month,
> I tried Datediff(wk, startofMonth ,EndOfMont)
> this is not giving correct output
> for example : Feb 1998 = 4ws
> july 2006 = 6 ws
> lara
>|||Hi Lara,
DateDiff function always assumes Sunday as the first day of a w.
So if you execute,
Select datediff(w, '2006-05-01', '2006-05-01') it would return only 4.
But actually there are 5 ws in this month. Its because the first sunday
starts on 7th of May only. Hope this clarifies your doubt on why the result
is not correct.
Best Regards
Vadivel
http://vadivel.blogspot.com
"aa" wrote:

> How can i write a Query to find out how many ws are there in a month,
> I tried Datediff(wk, startofMonth ,EndOfMont)
> this is not giving correct output
> for example : Feb 1998 = 4ws
> july 2006 = 6 ws
> lara
>
>|||Tim
You script returns 3 for May 2006, is it true?
DECLARE @.Today datetime
SET @.Today = getdate()
SELECT DATEDIFF(wk,f,l)
FROM
(
SELECT DATEADD(month, DATEDIFF(month, '1900', @.Today) + 1, '1900')AS f,
DATEADD(month, DATEDIFF(month, '1900', @.Today) + 2, '1900') - 1 AS l
) AS der
"Tim Jolliffe" <unlisted> wrote in message
news:XvKdna8AqqbEOffZnZ2dnUVZ_sydnZ2d@.co
mcast.com...
> --
> -- This will calculate the number of FULL ws
> -- (Sunday thru Saturday) in a month.
> --
> DECLARE @.FirstOfMonth datetime
> DECLARE @.FirstSunday datetime
> DECLARE @.LastOfMonth datetime
> DECLARE @.WsInMonth int
> SELECT @.FirstOfMonth = '05/01/2006' -- <<<<<<<<< Change this as
> needed.
> -- Get the last date of the month by adding 1 to the
> -- month and then subtracting 1 day.
> SELECT @.LastOfMonth = DATEADD(m, 1, @.FirstOfMonth)
> SELECT @.LastOfMonth = DATEADD(d, -1, @.LastOfMonth)
> -- Find the first Sunday in the month
> SELECT @.FirstSunday = CASE DatePart(dw, @.FirstOfMonth)
> WHEN '1' THEN @.FirstOfMonth
> WHEN '2' THEN DATEADD(d, 6, @.FirstOfMonth)
> WHEN '3' THEN DATEADD(d, 5, @.FirstOfMonth)
> WHEN '4' THEN DATEADD(d, 4, @.FirstOfMonth)
> WHEN '5' THEN DATEADD(d, 3, @.FirstOfMonth)
> WHEN '6' THEN DATEADD(d, 2, @.FirstOfMonth)
> WHEN '7' THEN DATEADD(d, 1, @.FirstOfMonth)
> END
> -- Calculate the number of ws between the first Sunday
> -- and the last date of the month.
> SELECT @.WsInMonth = (DATEDIFF(d, @.FirstSunday, @.LastOfMonth)+1) / 7
>
> SELECT @.WsInMonth
>
> "aa" <dearme23in@.yahoo.com> wrote in message
> news:OIUIbCWeGHA.1272@.TK2MSFTNGP03.phx.gbl...
>|||Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.programming:603586
Yes. There are 3 full Sunday thru Saturday ws in May. I'm not sure
if that's what she wanted.|||Thanks Uri,
This query also has the same problem , i've modified as
DECLARE @.Today datetime, @.EndDate datetime
SET @.Today ='01-jul-2006'
SET @.EndDate = '31-jul-2006'
SELECT DATEDIFF(wk,f,l)
FROM
(
SELECT DATEADD(month, DATEDIFF(month, '1900', @.Today) + 1, '1900')AS f,
DATEADD(month, DATEDIFF(month, '1900', @.EndDate) + 2, '1900') - 1 AS
l
) AS der
and is giving result as 4, while the answer is 6
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23NqkJzWeGHA.3556@.TK2MSFTNGP02.phx.gbl...
> Tim
> You script returns 3 for May 2006, is it true?
> DECLARE @.Today datetime
> SET @.Today = getdate()
> SELECT DATEDIFF(wk,f,l)
> FROM
> (
> SELECT DATEADD(month, DATEDIFF(month, '1900', @.Today) + 1, '1900')AS f,
> DATEADD(month, DATEDIFF(month, '1900', @.Today) + 2, '1900') - 1 AS
l
> ) AS der
>
>
> "Tim Jolliffe" <unlisted> wrote in message
> news:XvKdna8AqqbEOffZnZ2dnUVZ_sydnZ2d@.co
mcast.com...
month,
>|||Hi Tim,
I think my Question was not clear for you,I need the no of ws in a month
like this
for Feb 1998 -> there are 4 ws
for May 2006 (current month) -> there are 5 ws
For July 2006 -> it is 6
thanks Lara
"Tim Jolliffe" <tjolliffe@.hotmail.com> wrote in message
news:1147841502.933839.57740@.i39g2000cwa.googlegroups.com...
> Yes. There are 3 full Sunday thru Saturday ws in May. I'm not sure
> if that's what she wanted.
>|||Ok, see if this helps
CREATE FUNCTION dbo.WOfMonth(@.now_day DATETIME)
RETURNS INT
AS
BEGIN
RETURN DATEPART(w, @.now_day)
- DATEPART(w, CONVERT(CHAR(6), @.now_day, 112)+'01')
+ 1
END
--usage
SELECT dbo.WOfMonth ('20060731')
"lara169" <lara169@.gmail.com> wrote in message
news:eKxnxHXeGHA.2076@.TK2MSFTNGP04.phx.gbl...
> Thanks Uri,
> This query also has the same problem , i've modified as
> DECLARE @.Today datetime, @.EndDate datetime
> SET @.Today ='01-jul-2006'
> SET @.EndDate = '31-jul-2006'
> SELECT DATEDIFF(wk,f,l)
> FROM
> (
> SELECT DATEADD(month, DATEDIFF(month, '1900', @.Today) + 1, '1900')AS f,
> DATEADD(month, DATEDIFF(month, '1900', @.EndDate) + 2, '1900') - 1 AS
> l
> ) AS der
>
> and is giving result as 4, while the answer is 6
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23NqkJzWeGHA.3556@.TK2MSFTNGP02.phx.gbl...
> l
> month,
>|||Hi Lara,
You may try this.
I counted Number of Staurday and Number of Sundays
Max(Sat,Sun) using case expression if they are equal then (sat+1) will
be the number of ws.
Sequence table created for incrementing basedate
Select identity(int,1,1) seq into seq from sysobjects
Select Case When Sun>Sat Then Sun
When Sat>Sun Then Sat
When Sat=Sun Then Sat+1
End From
(
Select Count( Case When datepart(dw,IncDate) = 7 Then 1 else null end)
Sun,
Count( Case When datepart(dw,IncDate) = 6 Then 1 else null end) Sat
From
(
Select dateadd(d,seq-1,BaseDate) IncDate
>From seq ,(Select '20050801' BaseDate) Y
Where seq<33 and Month(dateadd(d,seq-1,BaseDate)) = Month(BaseDate)
And Year(dateadd(d,seq-1,BaseDate)) = Year(BaseDate)
) X
) XX
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com|||Thanks Uri,
Its still giving me wrong result
SELECT dbo.WOfMonth ('19960228')
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uf$ccQXeGHA.3348@.TK2MSFTNGP03.phx.gbl...
> Ok, see if this helps
> CREATE FUNCTION dbo.WOfMonth(@.now_day DATETIME)
> RETURNS INT
> AS
> BEGIN
> RETURN DATEPART(w, @.now_day)
> - DATEPART(w, CONVERT(CHAR(6), @.now_day, 112)+'01')
> + 1
> END
> --usage
> SELECT dbo.WOfMonth ('20060731')
>
> "lara169" <lara169@.gmail.com> wrote in message
> news:eKxnxHXeGHA.2076@.TK2MSFTNGP04.phx.gbl...
AS
AS
as
7
>sql