SQL Dates - Financial Year

[Ciphon]

Hi All,

I'm looking for a way to figure out the current financial year in SQL.

I'm creating a rolling report thats going to be emailed out via report centre once a month. If it were executed today, it should contain all details from '2010-04-01' and '2011-03-31'.

I want to avoid having to updating the dates manually in the report every year, but can't figure out how I'd work out the current financial year.

I've tried every combination of DATEPART, DATENAME, DATEDIFF, and GETDATE I can think of.. but I must be missing something.

Any ideas?

Thanks

 

[Ciphon]

Hah.. Agreed! I've actually just figured it out.. I actually need the last day of April (Just the way it's stored in this particular DB).

The way I did it:

SELECT CONVERT(DATE,DATEADD(D, - DATEPART(D, GETDATE()), DATEADD(M, 5, DATEADD(M, - DATEPART(M, GETDATE()), GETDATE()))))

 

[Ciphon]

I sometimes wish i could be bothered to set up an SQL server at home...but that's probably a bit sad.

I have one lol.

FYI for those who do need the first day of the month, rather than the last:

SELECT CONVERT(DATE, DATEADD(D, 1, DATEADD(D, - DATEPART(D, GETDATE()), DATEADD(M, 4, DATEADD(M, - DATEPART(M, GETDATE()), GETDATE())))))

 

[Ciphon]

Actually the above is incorrect.

If I used the above, then when we were between Jan 1st 2011 and March 31st 2011, it would have had a fit and not pulled any data, because it would be looking for April 2011.

So I've gone for a CASE in my WHERE clause:


MyDate >=
CASE
WHEN GETDATE() BETWEEN
CONVERT(DATE, DATEADD(D, 1, DATEADD(D, - DATEPART(D, GETDATE()), DATEADD(M, 1, DATEADD(M, - DATEPART(M, GETDATE()), GETDATE())))))
AND
CONVERT(DATE, DATEADD(D, 1, DATEADD(M, -1, DATEADD(D, - DATEPART(D, GETDATE()), DATEADD(M, 5, DATEADD(M, - DATEPART(M, GETDATE()), GETDATE()))))))
THEN CONVERT(DATE,DATEADD(YEAR, -1, DATEADD(D, - DATEPART(D, GETDATE()), DATEADD(M, 5, DATEADD(M, - DATEPART(M, GETDATE()), GETDATE())))))
ELSE CONVERT(DATE,DATEADD(D, - DATEPART(D, GETDATE()), DATEADD(M, 5, DATEADD(M, - DATEPART(M, GETDATE()), GETDATE()))))
END