create function dbo.fn_OccurrenceOfWeekdayInMonth(
-- input
@year int
, @month tinyint
, @weekday tinyint
, @occurrence tinyint
, @lastFirst bit
)
returns datetime
as
begin
-- local variables / validated input
declare @result datetime
, @yr int
, @mo tinyint
, @dw tinyint
;
-- processing
;
-- output
return @result ;
end
if (coalesce(@year, 0) not between 1753 and 9999) set @yr = year( getdate() )
else set @yr = @year
if (coalesce(@month, 0) not between 1 and 12) set @mo = month( getdate() )
else set @mo = @month
if (coalesce(@weekday, 0) not between 1 and 7) set @dw = 1
else set @dw = @weekday
with dates( dt )
as
(
-- select 1st day of month constructed from year and month inputs
select convert( datetime,
convert( char(8),
@yr * 10000 + @mo * 100 + 1
)
)
union all -- facilitates recursion
-- add in remainder of days in month
select dt + 1
from dates
-- keeps adding until the next day would be 1st of next month
where day( dt + 1 ) <> 1
)
select dt from dates ;
For September 2009, you should see these results :
, dates_tagged( dt, dw, occurrence, occurrence_reverse )
as
(
select dt, datepart( weekday, dt )
, row_number()
over( partition by datepart( weekday, dt )
order by dt )
, row_number()
over( partition by datepart( weekday, dt )
order by dt desc )
from dates
)
select dt, dw, occurrence, occurrence_reverse
from dates_tagged
;
Or, for your convenience, you can download and execute this T-SQL script :
alter function dbo.fn_OccurrenceOfWeekdayInMonth(
@year int
, @month tinyint
, @weekday tinyint
, @occurrence tinyint
, @lastFirst bit
)
returns datetime
as
begin
-- local variables
declare @result datetime
, @yr int
, @mo tinyint
, @dw tinyint
; -- handle invalid year value
if (coalesce(@year, 0) not between 1753 and 9999) set @yr = year( getdate() )
else set @yr = @year
; -- handle invalid month value
if (coalesce(@month, 0) not between 1 and 12) set @mo = month( getdate() )
else set @mo = @month
; -- handle invalid day of week value
if (coalesce(@weekday, 0) not between 1 and 7) set @dw = 1
else set @dw = @weekday
; -- construct date time common table expression to make things easier
with dates( dt )
as
(
-- select 1st day of month constructed from year and month inputs
select convert( datetime,
convert( char(8),
@yr * 10000 + @mo * 100 + 1
)
)
union all -- facilitates recursion
-- add in remainder of days in month
select dt + 1
from dates
-- keeps adding until the next day would be 1st of next month
where day( dt + 1 ) <> 1
)
-- utilize the cte of dates and tag each with occurrence value,
-- using ranking function(s)
, dates_tagged( dt, dw, occurrence, occurrence_reverse )
as
(
select dt, datepart( weekday, dt )
, row_number()
over( partition by datepart( weekday, dt )
order by dt )
, row_number()
over( partition by datepart( weekday, dt )
order by dt desc )
from dates
)
select @result = dt
from dates_tagged
where dw = @dw
and ((@lastFirst = 1 and occurrence_reverse = @occurrence)
or (coalesce(@lastFirst, 0) = 0 and occurrence = @occurrence))
; -- return result to caller
return @result ;
end
select dbo.fn_OccurrenceOfDaynameInMonth( 2009, 9, 1, 1, 0 ) ;
So, the first time the first day of the week (e.g., Sunday for me) occurs in September 2009 is:
2009-09-06 00:00:00.000
create function dbo.fn_OccurrenceOfDaynameInMonth(
@year int
, @month tinyint
, @dayname varchar(15)
, @occurrence tinyint
, @lastFirst bit
)
returns datetime
as
begin
-- local variables
declare @result datetime
, @weekday tinyint
declare @daynames table(
dayname varchar(15) primary key
, weekday tinyint
)
; -- translate day name to day of week number
set @weekday = 1 -- use week day variable temporarily as counter
while (@weekday <= 7)
begin
-- use result variable temporarily to store dates
set @result = getdate() - @weekday
insert into @daynames
values( datename( weekday, @result )
, datepart( weekday, @result )
)
set @weekday = @weekday + 1 -- increment counter
end
select @weekday = weekday
from @daynames
where dayname = @dayname
; -- utilize dbo.fn_OccurrenceOfWeekdayInMonth to get result
set @result =
dbo.fn_OccurrenceOfWeekdayInMonth(
@year, @month, @weekday, @occurrence, @lastFirst
)
;
return @result ;
end
select dbo.fn_OccurrenceOfDaynameInMonth( 2008, 2, 'Thursday', 1, 1 ) ;
So, the last (notice 1 for the @lastFirst parameter) Thursday in February 2008 is:
2008-02-28 00:00:00.000
-- Change language to French if in English system as I am;
-- users already in French system, remove both set lines.
set language French ;
-- Samedi == Saturday
select dbo.fn_OccurrenceOfDaynameInMonth( 2009, 2, 'Samedi', 1, 1 ) ;
set language English ;
Le dernier samedi de Février 2009 est:
2009-02-28 00:00:00.000
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (5)
Commented:
Author
Commented:Comes in handy every once in a while.
Author
Commented:For that you need a table of dates or numbers that you can associate to dates :
I showed one technique using recursive CTE above as part of the date occurrence solution, but here are some other articles that are good reads on this topic :
Delimited String Parsing in SQL Server 2005 and later
https://www.experts-exchange.com/A_192.html (see vw_nums)
Fun with MS SQL spt_values for delimited strings and virtual calendars
https://www.experts-exchange.com/A_1221.html
Commented:
soon it may come in handy for me..!
Author
Commented:Thank you for the vote also as well as others I had not yet acknowledged. I am just happy to have helped in some way.
Best regards,
Kevin