t-sql将日期分配到学年

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

我试图将缺席日期分配到学年,学年是8月1日到7月31日。

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

所以我想要的是: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.

有没有一种简单的方法在sql 2000服务器中执行此操作。

5 个解决方案

#1

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'

#2

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

#3

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

也许您可以考虑创建一个AcademicYear表,如下所示:

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.

作为附带福利,如果一年的开始和结束应该改变,那么这是一个数据变化,而不是一些可怕的扩展UDF。

#4

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.

每当您需要这样的任何类型的查询时,就可以轻松加入您的日历表。

#5

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')

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

etc

#1