Need Date function...
-
Does anyone have some code that will return a value corresponding to the number of Monday's in a month?
We have a business process that requires this value. For example for May 2004, the value returned would be 5.
Thanks
Rob P.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
-
You can try this. There is a more efficient way by doing the same to find the first monday and then, instead of adding 1 day, add 7 days/1 week, until your month changes, but I don't have time now to work this out.
Hope it helps,
Robert
-- Declare and set the required month and year
DECLARE @Month INT
DECLARE @Year INT
SELECT @Month = 5
SELECT @Year = 2004
-- Calculation starts here. You can move this in a function or SP
SET DATEFIRST 1 -- Set First day of week to Monday
DECLARE @Day INT
DECLARE @Date DATETIME
DECLARE @Counter INT
SELECT @Day = 1
SELECT @Counter = 0
-- Set up the first day of the month
SELECT @Date = CONVERT(DATETIME, CONVERT(VARCHAR(4),@Year) + RIGHT('00' + CONVERT(VARCHAR(2), @Month), 2) + RIGHT('00' + CONVERT(VARCHAR(2), @Day), 2))
WHILE Month(@Date) = @Month
BEGIN
IF DATEPART(dw,@Date) = 1 -- Is this a monday?
SELECT @Counter = @Counter + 1
SELECT @Date = DATEADD(dd,1,@Date) -- Set next date)
END
SELECT @Counter AS 'Number of Mondays'
-
Hi,
The following is pretty efficient...
FYI there are 52178 mondays in this millenium ( <3 sec to calculate ).
declare @yr int, @mth int
select @yr = 2004, @mth = 5
declare @startday datetime, @endday datetime, @weekday int, @wdcnt int
set @startday = convert(datetime,ltrim(@yr)+case when @mth < 10 then '0' else '' end+ltrim(@mth)+'01')
set @endday = dateadd(month,1,@startday)
set @weekday = 2 -- assumes mondays 2'nd day of week
set @wdcnt = 0
while( @startday < @endday )
begin
select @wdcnt = @wdcnt + case when datepart(weekday,@startday) = @weekday then 1 else 0 end,
@startday = @startday + 1
end
-- How many weekdays #2 do we have ?
select @wdcnt as my_mondays
/rockmoose
You must unlearn what You have learnt -
This function takes rockmoose's post one more step further (more of an internationalized step). You may not need to go this far, but food for thought.
CREATE FUNCTION dbo.GetDayOccurance (
@day TINYINT,
@yr INTEGER,
@mth INTEGER
)
RETURNS INTEGER
AS
BEGIN
DECLARE @strtDay DATETIME
DECLARE @endDay DATETIME
DECLARE @weekDay INTEGER
DECLARE @weekDayCnt INTEGER
DECLARE @frstDay INTEGER
DECLARE @sun TINYINT
DECLARE @mon TINYINT
DECLARE @tues TINYINT
DECLARE @weds TINYINT
DECLARE @thur TINYINT
DECLARE @fri TINYINT
DECLARE @sat TINYINT
-- Check to see what the first day of the week is
SET @frstDay = @@DATEFIRST
IF @frstDay = 1
BEGIN
SET @sun = 7
SET @mon = 1
SET @tues = 2
SET @weds = 3
SET @thur = 4
SET @fri = 5
SET @sat = 6
END
ELSE IF @frstDay = 2
BEGIN
SET @sun = 6
SET @mon = 7
SET @tues = 1
SET @weds = 2
SET @thur = 3
SET @fri = 4
SET @sat = 5
END
ELSE IF @frstDay = 3
BEGIN
SET @sun = 5
SET @mon = 6
SET @tues = 7
SET @weds = 1
SET @thur = 2
SET @fri = 3
SET @sat = 4
END
ELSE IF @frstDay = 4
BEGIN
SET @sun = 4
SET @mon = 5
SET @tues = 6
SET @weds = 7
SET @thur = 1
SET @fri = 2
SET @sat = 3
END
ELSE IF @frstDay = 5
BEGIN
SET @sun = 3
SET @mon = 4
SET @tues = 5
SET @weds = 6
SET @thur = 7
SET @fri = 1
SET @sat = 2
END
ELSE IF @frstDay = 6
BEGIN
SET @sun = 2
SET @mon = 3
SET @tues = 4
SET @weds = 5
SET @thur = 6
SET @fri = 7
SET @sat = 1
END
ELSE IF @frstDay = 7
BEGIN
SET @sun = 1
SET @mon = 2
SET @tues = 3
SET @weds = 4
SET @thur = 5
SET @fri = 6
SET @sat = 7
END
SET @strtDay = CONVERT(DATETIME, LTRIM(@yr) +
CASE
WHEN @mth < 10 THEN '0'
ELSE ''
END + LTRIM(@mth) + '01')
SET @endDay = DATEADD(MONTH, 1, @strtDay)
/* @day is based on the week days being numbered
Sunday = 1, Monday = 2, Tuesday = 3, Wednesday = 4
Thursday = 5, Friday = 6, Saturday = 7 */
IF @day = 1
SET @weekDay = @sun
IF @day = 2
SET @weekDay = @mon
IF @day = 3
SET @weekDay = @tues
IF @day = 4
SET @weekDay = @weds
IF @day = 5
SET @weekDay = @thur
IF @day = 6
SET @weekDay = @fri
IF @day = 7
SET @weekDay = @sat
SET @weekDayCnt = 0
WHILE (@strtDay < @endDay)
BEGIN
SELECT @weekDayCnt = @weekDayCnt + CASE
WHEN DATEPART(WEEKDAY, @strtDay) = @weekDay THEN 1
ELSE 0
END, @strtDay = @strtDay + 1
END
RETURN(@weekDayCnt)
END
-
Thanks for the Quick excellent response.
Here is another Haiku in gratitude.
A Phoenix arose
from my grandfather's ashes
"Got a light?", it said
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply