Function to get 2 dates

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:wave:

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

Code:

---

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

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.

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

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

Code:

---

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 :blush:

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:

Code:

---

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

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

Code:

---

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

---

A couple of alternatives

Code:

---

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