I recently had a little piece of SQL code that I thought I would post and it reminded me of something I did a long time ago (5 yrs) and thought I'd write something on it. Anyway, first here's the little piece without much celebration.
This SQL code answers the question "What date is the third Wednesday in November 2008?" This code could be wrapped in a function pretty easily.
declare @month int,@year int,@weekday int,@weekOffset int
set @month=11 -- (Month of date to check)
set @year=2008 -- (Year of date to check)
set @weekday=5 -- 1-7 (Sun-Sat)
set @weekOffset=1 -- 1-5 (Week of month)
declare @currentMonth datetime
declare @firstDay int
declare @newDate datetime
-- get the first day of the month
set @currentMonth=cast(cast(@month as char)+'/1/'+cast(@year as char) as datetime)
set @firstDay=datepart(dw,@currentmonth)
-- Add the number of days from the first day
set @newDate=dateadd(dd,7*(@weekOffset-1)+
case
when @weekday>=@firstDay then @weekday-@firstDay
when @weekday<@firstDay then 7+@weekday-@firstDay
end, @currentmonth)
select @newdate
The bigger idea I had was for recurring events in SQL, like for a calendar. Assume the following structure of an event table
CREATE TABLE dbo.Events(
ID int IDENTITY(1,1) NOT NULL ,
StartDate datetime,
EndDate datetime,
Title varchar(255),
Description varchar(2000),
RepeatValue char(1),
RepeatInterval int
)
So you have a bunch of events defined that start and end on a range of dates. The RepeatValue would be: D,W,M,Y for Day, Week, Month, Year. The RepeatInterval would be the number of Days/Weeks/Months between repeats. Add in some sample data
insert events select '1/1/08','12/31/08','Each Month All Year','','M',1
insert events select '1/1/08','6/30/08','Bi-weekly Half Year','','W',2
insert events select '7/1/08','12/31/08','Weekly Half Year','','W',1
insert events select '2/1/08','3/1/08','Every other Day in Feb','','D',2
insert events select '7/4/08','12/31/2999','July 4th','','Y',1
This is the stored proc I came up with to return the events for a given month:
CREATE Procedure [dbo].[sp_ListRecurringCalendarEvents]
@viewdate datetime
as
set nocount on
declare @checkdate datetime
create table #tempCalendar(
id int,
startdate datetime,
enddate datetime,
title varchar(255),
recurrance bit default 0
)
-- Insert Standard events
insert #tempCalendar
select e.id,e.startdate,e.enddate,e.title,0
from events e
where datepart(mm,e.startdate)=datepart(mm,@viewdate)
and datepart(yy,e.startdate)=datepart(yy,@viewdate)
and repeatinterval=0
-- Insert all recurrances
set @checkdate=cast(cast(datepart(mm,@viewdate) as char)
+ '/1/'
+ cast(datepart(yy,@viewdate) as char) as datetime)
-- Loop through each day in month and see if it is a recurrance
while datepart(mm,@viewdate)=datepart(mm,@checkdate)
begin
insert #tempCalendar
select e.id,
case when RepeatValue='D' then dateadd(dd,datediff(dd,startdate,@checkdate),startdate)
when RepeatValue='W' then dateadd(ww,datediff(ww,startdate,@checkdate),startdate)
when RepeatValue='M' then dateadd(mm,datediff(mm,startdate,@checkdate),startdate)
when RepeatValue='Y' then dateadd(yy,datediff(yy,startdate,@checkdate),startdate)
end,
e.enddate,e.title,
case when @checkdate=e.startdate then 0 else 1 end
from events e
where e.RepeatInterval<>0
and
-- Daily (Check to see if the number of day interval matches
case when RepeatValue='D' then (cast(datediff(dd,startdate,@checkdate) as decimal)
/cast(RepeatInterval as decimal)) else 1 end =
case when RepeatValue='D' then (datediff(dd,startdate,@checkdate)/RepeatInterval) else 1 end
and
-- Weekly (check to see if the week interval matches and the weekday is the same)
case when RepeatValue='W' then (cast(datediff(ww,startdate,@checkdate) as decimal)
/cast(RepeatInterval as decimal)) else 1 end =
case when RepeatValue='W' then (datediff(ww,startdate,@checkdate)/RepeatInterval) else 1 end
and case when RepeatValue='W' then datepart(dw,startdate) else 1 end =
case when RepeatValue='W' then datepart(dw,@checkdate) else 1 end
and
-- Monthly (check to see that the month interval is the same and the day of the month is the same)
case when RepeatValue='M' then (cast(datediff(mm,startdate,@checkdate) as decimal)
/cast(RepeatInterval as decimal)) else 1 end =
case when RepeatValue='M' then (datediff(mm,startdate,@checkdate)/RepeatInterval) else 1 end
and case when RepeatValue='M' then datepart(dd,startdate) else 1 end =
case when RepeatValue='M' then datepart(dd,@checkdate) else 1 end
and
-- Yearly (Check to see if the day of the year is the same)
case when RepeatValue='Y' then datepart(dy,startdate) else 1 end =
case when RepeatValue='Y' then datepart(dy,@checkdate) else 1 end
and
e.startdate <= case when RepeatValue='D' then dateadd(dd,datediff(dd,startdate,@checkdate),startdate)
when RepeatValue='W' then dateadd(ww,datediff(ww,startdate,@checkdate),startdate)
when RepeatValue='M' then dateadd(mm,datediff(mm,startdate,@checkdate),startdate)
when RepeatValue='Y' then dateadd(yy,datediff(yy,startdate,@checkdate),startdate)
end
and
e.enddate >= case when RepeatValue='D' then dateadd(dd,datediff(dd,startdate,@checkdate),startdate)
when RepeatValue='W' then dateadd(ww,datediff(ww,startdate,@checkdate),startdate)
when RepeatValue='M' then dateadd(mm,datediff(mm,startdate,@checkdate),startdate)
when RepeatValue='Y' then dateadd(yy,datediff(yy,startdate,@checkdate),startdate)
end
-- Increment the date
set @checkdate=dateadd(dd,1,@checkdate)
end
-- return the results
select * from #tempCalendar
-- Clean up
drop table #tempCalendar
The proc is called passing a date within the month to be displayed. The results include the ID of the event (for linking), the start date of the event, the end date of the event (not so relevant), the event title, and whether this is a recurring event or not (in case you want to display it differently or disable editing of events that aren't the original).
So, many years later, I still find it intriguing that I was doing stuff like that.