More term dates

Here are two functions/procedures to use together with the provisional term dates functions I posted in:

http://www.cs.ox.ac.uk/blogs/edward-crichton/2012/05/30/full-term-dates/

for converting to and from Oxford term dates.


# get the date for: a term and a week and a day of week, with 1..7 being sun-sat. (2..6 = mon-fri)
# e.g. you can get Thursday of week 0 Michaelmas term with: SELECT getDateForTermWeekDay(2012,'MT',0,5);

DROP FUNCTION IF EXISTS getDateForTermWeekDay;

delimiter //

create function getDateForTermWeekDay(year int(5),term char(2),weekInTerm int(2),dayOfWeek int(1)) RETURNS date
main:BEGIN
	declare termStart date;
	
	IF term NOT IN ('MT','HT','TT')
	THEN
		RETURN NULL;
	END IF;
	
	IF term='MT'
	THEN
		SET termStart=michaelmas(year);
	END IF;
	
	IF term='HT'
	THEN
		SET termStart=hilary(year);
	END IF;
	
	IF term='TT'
	THEN
		SET termStart=trinity(year);
	END IF;
	
	RETURN DATE_ADD(DATE_ADD(termStart, INTERVAL (weekInTerm-1) WEEK), INTERVAL (dayOfWeek-1) DAY);

END//

delimiter ;

DROP PROCEDURE IF EXISTS getTermWeekDayForDate;

delimiter //

# returns year, term, week and day, with week starting from week '-1' onwards.
# e.g. call getTermWeekDayForDate('2012-10-04');

create procedure getTermWeekDayForDate(IN date_in date)
main:BEGIN
	
	DECLARE Y int(5);
	DECLARE MT date;
	DECLARE HT date;
	DECLARE TT date;
	DECLARE term char(2);
	DECLARE relative date;
	DECLARE WEEK_DEC decimal(5,3);
	DECLARE WEEK int(2);
		
	SET Y=EXTRACT(YEAR FROM date_in);
	
	SET MT=michaelmas(Y);
	SET HT=hilary(Y);
	SET TT=trinity(Y);
	
	IF date_in >=DATE_SUB(MT,INTERVAL 2 WEEK)
	THEN
		SET term='MT';
		SET relative=MT;
	ELSEIF date_in >=DATE_SUB(TT,INTERVAL 2 WEEK)
	THEN
		SET term='TT';
		SET relative=TT;
	
	
	ELSEIF date_in >=DATE_SUB(HT,INTERVAL 2 WEEK)
	THEN
		SET term='HT';
		SET relative=HT;
	
	END IF;
	
	SET WEEK_DEC=FLOOR((((DATEDIFF(date_in,DATE_SUB(relative,INTERVAL 2 WEEK)))-14)/7))+1;
	SET WEEK=WEEK_DEC;
	
	SELECT Y AS Year,term AS Term,WEEK AS Week, DAYOFWEEK(date_in) AS Day;
END//

delimiter ;
mysql> SELECT getDateForTermWeekDay(2012,'MT',0,5);
+--------------------------------------+
| getDateForTermWeekDay(2012,'MT',0,5) |
+--------------------------------------+
| 2012-10-04                           | 
+--------------------------------------+

mysql> call getTermWeekDayForDate('2012-10-04');
+------+------+------+------+
| Year | Term | Week | Day  |
+------+------+------+------+
| 2012 | MT   |    0 |    5 | 
+------+------+------+------+

Leave a Reply