Academic Year function

As an exercise in ‘Clean code’ this is about the best I could do:-

--fnGetAcademicYear.sql


/* Returns academic year of supplied date */
CREATE FUNCTION [dbo].[fn_Get_AcademicYear]
(
   @in DATE
)
RETURNS INT
AS
BEGIN

  DECLARE @out INT;

  SELECT @out=CASE WHEN DATEPART(MONTH, @in) > 7 
              THEN CONCAT(YEAR(@in), YEAR(@in) + 1)
              ELSE CONCAT(YEAR(@in) - 1, YEAR(@in))
              END;

  RETURN @out;

END;
GO

With these sorts of things, traditionally you say item1 + item2 + number, where you want item1 bolted onto item2, and item2 to have had some maths done to it. The ‘+’ symbol serving as both a combiner and a mathematical operator.

To force ‘+’ to be a combiner and not a maths operator you use CONVERT or CAST to change the objects to text strings.

Happily CONCAT is an alternative we can use to get away from the confusing dual purpose of ‘+’. And clean up all those conversions. This operator only joins items together, and never does maths on them. 🙂