Obtaining academic year from date field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Obtaining academic year from date field

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2007-11-15 : 06:26:09

Hello

I'm trying to obtain an academic year from a module start date.

Example:
If an acedemic module start date is 10/10/06 i want it to return that it is in the academic year 2006/07. If a module start date is 02/02/2006 then I want it to return the academic year 2005/06, etc.

The academic year runs from 01 August to 31 July

I'm sure this must be acheivable using the substring function or would I have to use CASE WHEN using between dates for each individual academic year?

Thanks

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-15 : 06:44:14

i think i'd do it like this...

declare @date datetime

set @date = '20071001'

select @date, 
case when month(@date) >=8 then cast(year(@date) as varchar(20)) + '/' + cast(year(@date)+1 as varchar(20)) 
     when month(@date) <=7 then cast(year(@date)-1 as varchar(20)) + '/' + cast(year(@date) as varchar(20))
     else null end


which gives...
2007-10-01 00:00:00.000	2007/2008

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-15 : 06:46:29

i'd probably write it as a function too, so you could just pass in a date

Em

bendertez
Yak Posting Veteran

94 Posts

Posted - 2007-11-15 : 07:08:14

Thanks Em

Thats what I wanted rather than have loads of CASE WHEN lines for each year.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources