September 3, 2008 at 11:55 am
I want to create a user defined function which takes in the date and gives the number of weeks for that month .And no of weeks in amonth is no of sundays and i Dont count it from 1st.
i am Counting the no of sundays and return the no of sundays which will be my answer
Example
If i supply the date sep 02 2008 ot shuold give me 4 weeks as we have 4 sundays
August it is going to be 5 Sundays
i got some date functions but not sure how to implement it in my code.
The date functions are
dateadd
datediff
datepart
Some of this functions can help you.so i would appreciate if you guys can help me out.
thank you
September 3, 2008 at 1:04 pm
-- =============================================
-- Author: Parackson
-- Create date: 9/3/2008
-- The following will return the number of sundays in a given month depending on the incoming date value
-- =============================================
CREATE FUNCTION dbo.CountSundaysInMonth(@inDate AS SMALLDATETIME)
RETURNS INT
AS
BEGIN
DECLARE @FirstDay AS SMALLDATETIME
DECLARE @LastDay AS SMALLDATETIME
DECLARE @RunningDay AS SMALLDATETIME
DECLARE @Days AS INT
--SET @FirstDay TO BE THE FIRST DAY OF THE MONTH
SET @FirstDay= CONVERT(SMALLDATETIME, CAST(DATEPART(YY, @inDate) AS VARCHAR) + '/' + CAST(DATEPART(MM, @inDate) AS VARCHAR) + '/01' )
--SET @LastDay TO BE THE LAST DAY OF THE MONTH
SET @LastDay = DATEADD(DD,-1, DATEADD(MM, 1, @FirstDay))
SET @Days=0
SET @RunningDay = @FirstDay
--LOOP UNTIL @RunningDay IS GREATER THAN @LastDay
WHILE (@RunningDay <= @LastDay)
BEGIN
--IF DATEPART OF @RunningDay = 1(Sunday) ADD 1 TO @dAYS
IF DATEPART(dw,@RunningDay) = 1
BEGIN
SET @Days = @Days + 1
END
--INCREASE @RunningDay BY ONE DAY
SET @RunningDay = DATEADD(DD, 1, @RunningDay)
END
--RETURN NUMBER OF SUNDAYS IN A MONTH
RETURN (@Days)
END
GO
September 3, 2008 at 1:25 pm
Hey parackson.
thanks a lot for your prompt and awesome reply.it exactly answered my query.i also figured out a way to do it something similar to yours just now.i am sharing that to you all.thanks once again dude for your help.
--Function CODE:
Alter Function CountWeeks(@date datetime)
Returns int
As
Begin
Declare @weeks int,@Week_firstday int ,@Week_lastday int,@Month_firstday int,@Month_lastday int
Set @Week_firstday=Datepart(dw,dateadd(mm,datediff(mm,0,@date),0))
Set @Week_lastday=datepart(dw,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@date)+1,0)))---getdate())+1,0)))
Set @Month_Firstday=datepart(dd,dateadd(mm,datediff(mm,0,getdate()),0))--@date),0))
Set @Month_Lastday=datepart(dd,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)))--@date)+1,0)))
SET @weeks=DATEDIFF(dd,@Month_firstday,@Month_lastday)/7 -- to find the no of weeks between first and last day
If(@Week_firstday=1 or @Week_Lastday=1)
Set @weeks=@weeks+1
Else
If(@Week_firstday=7 and @Week_Lastday=2)
Set @weeks=@weeks+1
Else
Set @weeks=@weeks+0
return @weeks
end
Select dbo.Countweeks('2008-03-1')
Select dbo.Countweeks(getdate())
September 3, 2008 at 1:26 pm
no problem thats what this site is for.
September 3, 2008 at 1:34 pm
This shows a different method that only requires a single select statement.
select
[Year]= year(FirstSunday),
[Month] = month(FirstSunday),
SundaysInMonth = (datediff(dd,FirstSunday,LastSunday)/7)+1
from
(
select
*,
FirstSunday =
-- First Sunday of the Month
dateadd(dd,((datediff(dd,-53684,aa.Day7)/7)*7),-53684),
LastSunday =
-- Last Sunday of the Month
dateadd(dd,((datediff(dd,-53684,aa.EOM)/7)*7),-53684)
from
(
select
Day7 =
-- Day 7 of the Month
dateadd(mm,datediff(mm,0,aaa.DT),0)+6,
EOM =
-- Last day of the month
dateadd(mm,datediff(mm,-1,aaa.DT),-1)
from
( -- Test dates, all months for 2008
select DT = convert(datetime,'20080102') union all
select DT = convert(datetime,'20080205') union all
select DT = convert(datetime,'20080307') union all
select DT = convert(datetime,'20080411') union all
select DT = convert(datetime,'20080513') union all
select DT = convert(datetime,'20080615') union all
select DT = convert(datetime,'20080717') union all
select DT = convert(datetime,'20080819') union all
select DT = convert(datetime,'20080921') union all
select DT = convert(datetime,'20081023') union all
select DT = convert(datetime,'20081125') union all
select DT = convert(datetime,'20081231')
) aaa
) aa
) a
Results:
Year Month SundaysInMonth
----------- ----------- --------------
2008 1 4
2008 2 4
2008 3 5
2008 4 4
2008 5 4
2008 6 5
2008 7 4
2008 8 5
2008 9 4
2008 10 4
2008 11 5
2008 12 4
(12 row(s) affected)
September 3, 2008 at 1:43 pm
This does the same as my last post. It just has the functions calls for SundaysInMonth nested.
select
[Year]= year(a.DT),
[Month] = month(a.DT),
SundaysInMonth =
(datediff(dd,
dateadd(dd,((datediff(dd,-53684,dateadd(mm,datediff(mm,0,a.DT),0)+6)/7)*7),-53684),
dateadd(dd,((datediff(dd,-53684,dateadd(mm,datediff(mm,-1,a.DT),-1))/7)*7),-53684)
)/7)+1
from
( -- Test dates, all months for 2008
select DT = convert(datetime,'20080102') union all
select DT = convert(datetime,'20080205') union all
select DT = convert(datetime,'20080307') union all
select DT = convert(datetime,'20080411') union all
select DT = convert(datetime,'20080513') union all
select DT = convert(datetime,'20080615') union all
select DT = convert(datetime,'20080717') union all
select DT = convert(datetime,'20080819') union all
select DT = convert(datetime,'20080921') union all
select DT = convert(datetime,'20081023') union all
select DT = convert(datetime,'20081125') union all
select DT = convert(datetime,'20081231')
) a
September 3, 2008 at 1:46 pm
This seems to be a cool way with just one select statement.I really like this forum and you guys are making this lively.myself vijay ,i am like a fresher trying to learn sql server .so you guys are helping me a lot.thanks a lot for all you support.thanks SSeight.
have a nice day.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply