Function to get 2 dates

[venerable bede]

SQL Server 2008

I am writing some sql for an academic institution.
Some of the sql must extract records between 2 dates.
These dates represent the beginning and end of the academic yesr.
Basically if the current date is before the 1st Sept then the start date will be the 1st Sept last year and the end date the 30th Sept this year else it will be the 1st of Sept this year and the 30th next year.

How can I get these dates?



Thanks in Advance

[techgnome]

by using a case statement and the datediff function...

case 
  when getdate() < convert(datetime, convert(varchar(4), year(getdate()) + '-09-01') then  convert(datetime, convert(varchar(4), (year(getdate())-1) + '-09-01') 
  else convert(datetime, convert(varchar(4), year(getdate()) + '-09-01') 
end as startDate

for the end date, it's similar logic... just change the 90-01 to 08-30 (I think you meant the end of august, not the end of September, otherwise you end up with an overlap.

-tg

[venerable bede]

I take it I can wrap this up in a function.
I'm not in work at the moment but I'll mess around with this tomorrow but 'm stil not sure why I need CASE.
Thanks for that.

[techgnome]

ummm.... how else do you do it? you can't use an if in a select... but you can use a case statement.

you could wrap it up in a function, but it's probably not necessary. Depends on if you only need it for a single query or if you need it more often than not.

-tg

[venerable bede]

Well I just got in and I have created a function :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION fn_get_semester_startDate 
(
	
)

RETURNS datetime 
AS
BEGIN
	-- Declare the return variable here
	DECLARE @@StartDate datetime

case 
  when getdate() < convert(datetime, convert(varchar(4), year(getdate())) + '-09-01') then  convert(datetime, convert(varchar(4), (year(getdate())-1) + '-09-01') 
  else convert(datetime, convert(varchar(4), year(getdate()) + '-09-01') 
end as @@StartDate

	-- Return the result of the function
	RETURN @@StartDate

END
GO

I keep getting:
Incorrect syntax near the keyword 'case'.
Must declare the scalar variable "@@StartDate".
Sorry to hastle you but as you may have guessed it's been a while since I have done any db work....i am an idiot

[techgnome]

case isn't a CONTROL structure the way the IF statement is.... it's part of the SELECT construct... http://msdn.microsoft.com/en-us/library/ms181765.aspx

the way I INTENDED the original code snip to be used was like this:

Select 
  Field1, 
  Field2, 
  case 
    when getdate() < convert(datetime, convert(varchar(4), year(getdate())) + '-09-01') then  convert(datetime, convert(varchar(4), (year(getdate())-1) + '-09-01') 
    else convert(datetime, convert(varchar(4), year(getdate()) + '-09-01') 
  end as StartDate
from SomeTable

That's why I said a function isn't quite necessary...

-tg

[venerable bede]

Interesting.
Anyways I changed it slightly and created a UDF:

ALTER FUNCTION [dbo].[fn_get_startDate]
(
	
)

RETURNS datetime 
AS
BEGIN
	-- Declare the return variable here
	DECLARE @@StartDate datetime
	DECLARE @@FirstSept datetime

SET @@FirstSept = convert(datetime, convert(varchar(4), year(getdate())) + '-09-01')

if getdate() > convert(datetime,convert(int,@@FirstSept))
	set @@StartDate = convert(varchar(4),year(getdate())-1) + '-09-01'

	RETURN @@StartDate

END

[techgnome]

A couple of alternatives

CREATE FUNCTION ufn_get_StartDate()
RETURNS DateTime
AS
BEGIN
	-- Declare the return variable here
	DECLARE @RetValue datetime

	-- Add the T-SQL statements to compute the return value here
	SELECT 
		@RetValue = case 
							when getdate() < convert(datetime, convert(varchar(4), year(getdate())) + '-09-01')
								then  convert(datetime, convert(varchar(4), (year(getdate())-1)) + '-09-01')
							else convert(datetime, convert(varchar(4), year(getdate())) + '-09-01')
					end

	-- Return the result of the function
	RETURN @RetValue

END
GO

CREATE FUNCTION ufn_get_StartDate2()
RETURNS DateTime
AS
BEGIN

	-- Add the T-SQL statements to compute the return value here
	return  case 
				when getdate() < convert(datetime, convert(varchar(4), year(getdate())) + '-09-01')
					then  convert(datetime, convert(varchar(4), (year(getdate())-1)) + '-09-01')
				else convert(datetime, convert(varchar(4), year(getdate())) + '-09-01')
			  end
END
GO

selects are going to run faster than control statements... that last one should be most optimal as it's basically a single line statement executed, with no control structure, it just simply runs the formula and returns immediately.

-tg