2

So there is a very similar forum post to this one but I cannot run those commands. I am using SQL Server 2008 R2 and need help getting the current school year.

Basically I need a way to generate the current school year by using the current date. From Aug. of the current year until Aug. of the next year it will be 1 school year.

Ex. Aug. 2015 - July 2016 will need to pull up the year 2015 and Starting Aug. 2016 the year 2016 will need to pull up.

I am still fairly new to SQL so any help would be greatly appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
John
  • 23
  • 3
  • Do you need to include this in statement? Procedure? – Kamil Gosciminski Jan 08 '16 at 20:06
  • You might have more luck searching for "fiscal year", such as this: http://stackoverflow.com/questions/1771995/calculate-fiscal-year-in-sql-server – Rick Liddle Jan 08 '16 at 20:06
  • Most student information systems I've worked on have a calendar table that defines the School Year (however the SIS defines it), as well as the start dates and end dates for the school year. The SIS I work on currently has one table for the school year, one for marking period dates, and one table with every day in the school year itself to determine membership and attendance. Our SIS breaks all the calendars down by building as well to handle odd situations like power failures or water main breaks. – Bacon Bits Jan 08 '16 at 20:24

2 Answers2

1

Sounds like you're looking for this:

IF Month(GetDate()) >=8
BEGIN
    SELECT Year(GetDate())
END
ELSE
BEGIN
    SELECT Year(GetDate())-1
END

EDIT:

To use this in a select statement's where clause:

DECLARE @currentSchoolYear INT

IF Month(GetDate()) >=8
BEGIN
    SELECT @currentSchoolYear = Year(GetDate())
END
ELSE
BEGIN
    SELECT @currentSchoolYear = Year(GetDate())-1
END

SELECT * FROM dbo.dates
WHERE CASE WHEN Month(datevalue) >=8 THEN Year(datevalue)
ELSE Year(datevalue)-1
END = @currentSchoolYear

obviously replace "dbo.dates" with your table name, and "datevalue" with whatevercolumn you're comparing. This will return the rows from the current school year

Jeffrey Van Laethem
  • 2,601
  • 1
  • 20
  • 30
  • So quick question. How would I incorporate this into the where section of my query? I need to be able to tell the query to pull only the records corresponding to the proper school year WHERE the school year is defined by your above statement. – John Jan 08 '16 at 23:11
  • I am assuming I can declare @currdate as a INT then SELECT that INT based on the getdate() field. – John Jan 08 '16 at 23:26
  • Edited my answer with an example in a select statement. – Jeffrey Van Laethem Jan 08 '16 at 23:52
-2
SELECT value1, value, 
    (CASE WHEN GETDATE() BETWEEN '20150801' AND '20160731' THEN 2015
          WHEN GETDATE() BETWEEN '20160801' AND '20170731' THEN 2016
          ELSE 2014) AS schoolYear
FROM dbTable
WHERE ...
PKatona
  • 629
  • 2
  • 9
  • 19
  • 1
    This certainly won't work outside the date range you specified, and assumes there's a table involved. – Jeffrey Van Laethem Jan 08 '16 at 20:17
  • No it won't; however, having been in academia for many years, a date based approach with more options may be preferable since school years usually begin mid-month. If the beginning of month holds fast, then your approach is certainly superior to mine. but it seems to me giving someone negatives is a bit extreme. If anyone from the colleges I've worked at used your approach it would fail. – PKatona Jan 08 '16 at 20:40
  • Our school years do begin/end mid month; however, for this situation I think either solution will work just fine! Thank you so much for your response's. – John Jan 08 '16 at 22:46