t-sql assign dates to academic year

I am trying to assign absence dates to an academic year, the academic year being 1st August to the 31st July.

So what I would want would be:
31/07/2007 = 2006/2007
02/10/2007 = 2007/2008
08/01/2008 = 2007/2008

Is there an easy way to do this in sql 2000 server.

From stackoverflow

• Should work this way:

  select case 
      when month(AbsenceDate) <= 7 then 
       ltrim(str(year(AbsenceDate) - 1)) + '/' 
                 + ltrim(str(year(AbsenceDate)))
          else 
       ltrim(str(year(AbsenceDate))) + '/' 
                 + ltrim(str(year(AbsenceDate) + 1))
          end
  

  Example:

  set dateformat ymd
  declare @AbsenceDate datetime
  set @AbsenceDate = '2008-03-01'
  select case 
      when month(@AbsenceDate) <= 7 then 
       ltrim(str(year(@AbsenceDate) - 1)) + '/' 
                 + ltrim(str(year(@AbsenceDate)))
          else 
       ltrim(str(year(@AbsenceDate))) + '/' 
                 + ltrim(str(year(@AbsenceDate) + 1))
          end
  

• A variant with less string handling

  SELECT
    AbsenceDate,
    CASE WHEN MONTH(AbsenceDate) <= 7 
      THEN 
        CONVERT(VARCHAR(4), YEAR(AbsenceDate) - 1) + '/' + 
        CONVERT(VARCHAR(4), YEAR(AbsenceDate))
      ELSE 
        CONVERT(VARCHAR(4), YEAR(AbsenceDate)) + '/' + 
        CONVERT(VARCHAR(4), YEAR(AbsenceDate) + 1)
    END AcademicYear
  FROM
    AbsenceTable
  

  Result:

  2007-07-31 => '2006/2007' 
  2007-10-02 => '2007/2008' 
  2008-01-08 => '2007/2008'
  

• You could create a function as follows:

  CREATE FUNCTION dbo.GetAcademicYear(@in DATETIME)
  RETURNS VARCHAR(10) AS
  BEGIN
      DECLARE @out VARCHAR(10)
  
      IF (MONTH(@in) > 7)
          SET @out = CAST(YEAR(@in) AS VARCHAR) + '/' + CAST((YEAR(@in) + 1) AS VARCHAR)
      ELSE
          SET @out = CAST((YEAR(@in) - 1) AS VARCHAR) + '/' + CAST(YEAR(@in) AS VARCHAR)
  
      RETURN(@out)
  END
  

  And then just call it with:

  SELECT dbo.GetAcademicYear('31 July 2007')
  

  or

  SELECT col1, col2, dbo.GetAcademicYear(date_col) AS academic_year, col3, etc
  FROM my_table
  

  etc

• Maybe you could consider creating an AcademicYear table, something like this:

  CREATE TABLE AcademicYear (
      AcYear   VARCHAR(9)
  ,   FromDate DATE,
  ,   ToDate   DATE)
  

  and populating it accordingly. Then your query might become

  SELECT
    AbsenceDate,
    AcYear
  FROM
    AbsenceTable JOIN AcademicYear ON AbsenceDate BETWEEN FromDate AND ToDate
  

  As a fringe benefit, if the start and end of the year should change, it's a data change, not some horrible extended UDF.

• I would suggest creating a calendar table. It would include all dates for the foreseeable future (and past) with any information for those dates that you need - such as the academic year to which it belongs. You can then also add in things like whether or not it's a holiday, a weekend, etc.

  Whenever you need any kind of query like this it's then a simple matter of joining to your calendar table.