Full term dates

I’ve just fallen off my chair. I’ve managed to calculate the dates of full term programmatically. No more keeping tables of dates, or at least no more entering them.

I should share this so, here are the stored functions for mysql:

# returns where easter is according to the Carter algorithm
DROP FUNCTION IF EXISTS easter;

delimiter //

create function easter(Y int(5))
	RETURNS date
	
main:BEGIN

	DECLARE D int(4);
	DECLARE E int(4);
	DECLARE Q int(4);
	
	SET D=225-(11 * (Y MOD 19));
	
	WHILE D > 50 DO
	
		SET D=D-30;
	
	END WHILE;

	IF D > 48
	THEN
		SET D=D-1;
	END IF;
	
	
	SET E = (Y + FLOOR(Y/4) + D + 1) MOD 7;
	
	SET Q=D + 7 - E;
	
	IF Q<32
	THEN
	
		return CONCAT(Y,'-','03','-',Q);
	
	ELSE
	
		return CONCAT(Y,'-','04','-',(Q-31));
	
	END IF;

END//

delimiter ;

# term dates
# "Michaelmas, Term shall begin on and include 1 October and end on and include 17 December."
# Full term actually appears to be the first Sunday after the first Monday
# find the first Monday in October and add 6 days.


DROP FUNCTION IF EXISTS michaelmas;

delimiter //

create function michaelmas(Y int(5))
	RETURNS date
	
main:BEGIN


	IF (2-DAYOFWEEK(CONCAT(Y,'-10-01')))>=0
	THEN
		RETURN DATE_ADD(DATE_ADD( CONCAT(Y,'-10-01'), INTERVAL ( 2- DAYOFWEEK(  CONCAT(Y,'-10-01') ) ) DAY ), INTERVAL 6 DAY);
	ELSE
		RETURN DATE_ADD(DATE_ADD(CONCAT(Y,'-10-01'), INTERVAL ( 9 - DAYOFWEEK(CONCAT(Y,'-10-01') ) ) DAY), INTERVAL 6 DAY);
	END IF;
	
END//

delimiter ;

# "Hilary, Term shall begin on and include 7 January and end on and include 25 March or the Saturday before Palm Sunday, whichever is the earlier."
# Full term actually appears to be the first Sunday after the first Monday after 7th January

DROP FUNCTION IF EXISTS hilary;

delimiter //

create function hilary(Y int(5))
	RETURNS date
	
main:BEGIN


	IF (2-DAYOFWEEK(CONCAT(Y,'-01-07')))>=0
	THEN
		RETURN DATE_ADD(DATE_ADD( CONCAT(Y,'-01-07'), INTERVAL ( 2- DAYOFWEEK(  CONCAT(Y,'-01-07') ) ) DAY ), INTERVAL 6 DAY);
	ELSE
		RETURN DATE_ADD(DATE_ADD(CONCAT(Y,'-01-07'), INTERVAL ( 9 - DAYOFWEEK(CONCAT(Y,'-01-07') ) ) DAY), INTERVAL 6 DAY);
	END IF;
	
END//

delimiter ;

# "Trinity, Term shall begin on and include 20 April or the Wednesday after Easter, whichever is the later, and end on and include 6 July."
# Full term actually appears to be the first Sunday after the latest of (20th April or Wednesday after Easter)

DROP FUNCTION IF EXISTS trinity;

delimiter //

create function trinity(Y int(5))
	RETURNS date
	
main:BEGIN
	DECLARE latest date;
	DECLARE twentyTh date;
	
	SET twentyTh=CONCAT(Y,'-04-20');
	
	# Wednesday after Easter
	SET latest=DATE_ADD(easter(Y), INTERVAL 3 DAY);
	
	IF latest < twentyTh
	THEN
	
		SET latest=twentyTh;
	
	END IF;

	IF (1-DAYOFWEEK(latest))>=0
	THEN
		RETURN DATE_ADD( latest, INTERVAL ( 1- DAYOFWEEK(  latest ) ) DAY );
	ELSE
		RETURN DATE_ADD(latest, INTERVAL ( 8 - DAYOFWEEK(latest ) ) DAY);
	END IF;
	
END//

delimiter ;

So if I want the term dates for the academic year starting MT2012 I could query:

SELECT michaelmas(2012),hilary(2013),trinity(2013);
+------------------+--------------+---------------+
| michaelmas(2012) | hilary(2013) | trinity(2013) |
+------------------+--------------+---------------+
| 2012-10-07       | 2013-01-13   | 2013-04-21    | 
+------------------+--------------+---------------+

Officially (which this is not), the dates for full term are prescribed by Council and then published by the Registrar to the University Gazette, and they may be different from the provisional dates. But it seems to be infrequent that the provisional dates are changed. Which, at least, makes this useful for entering dates in advance.

Leave a Reply