SQL Dates - Financial Year

Associate
Joined
9 Dec 2008
Posts
2,341
Location
Hampshire
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
 
Associate
Joined
18 Nov 2008
Posts
2,430
Location
Liverpool
Can't you just compare the current year with the year in the SQL statement, and add "WHERE Date > '".$CurYear>"'"

With the following before hand: $CurYear = $Year."-04-01"

And have $Year assigned as the first 4 characters of DATE?

Sorry I can't write anything up properly, but the OcUK input box is hardly the best place to code ^^
 
Associate
OP
Joined
9 Dec 2008
Posts
2,341
Location
Hampshire
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()))))
 
Last edited:
Associate
Joined
20 Feb 2007
Posts
131
I use Oracle not Microsoft SQL. If i was looking at this problem I would probably make 2 functions. 1 to get the year start date, and 1 to get the year end date - then use a between statement. Something like below (although this definately wont work out of the tin - written in notepad :p).

Code:
Function get_year_start(sysdate)

DECLARE @date DATETIME

    Case when month(sysdate) > 3 then   
        SET @date = year(sysdate) + '-04-01'
    ELSE
        SET @date = (year(sysdate)-1) + -04-01'        
    END;
    Return @date;
End function



Function get_year_end(sysdate)

DECLARE @date DATETIME

    Case when month(sysdate) > 3 then   
        SET @date = (year(sysdate)+1) + '-03-31'
    ELSE
        SET @date = year(sysdate)-1 + -03-31'        
    END;
    Return @date;
End function


SELECT
'your stuff
FROM
'your table
WHERE
'your date between get_year_start(sysdate) and get_year_end(sysdate)

I sometimes wish i could be bothered to set up an SQL server at home...but that's probably a bit sad. :(
 
Associate
OP
Joined
9 Dec 2008
Posts
2,341
Location
Hampshire
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())))))
 
Associate
OP
Joined
9 Dec 2008
Posts
2,341
Location
Hampshire
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
 
Associate
Joined
23 Feb 2012
Posts
1
you can try below global script,

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = DATEADD(dd,0, DATEDIFF(dd,0, DATEADD( mm, -(((12 + DATEPART(m, getDate())) - 4)%12), getDate() ) - datePart(d,DATEADD( mm, -(((12 + DATEPART(m, getDate())) - 4)%12),getDate() ))+1 ) )

SET @EndDate = DATEADD(SS,-1,DATEADD(mm,12,@StartDate))


SELECT @StartDate,@EndDate
 
Soldato
Joined
28 Aug 2006
Posts
3,003
I didn't think that it made a difference how dates are stored if you use the ISO format 'YYYY-MM-DD"

My pseudo code:
Code:
Is current month > March AND < January

	currentFinancialYearStart = year(today) & "-APR-01"

	currentFinancialYearEnd = year(today) + 1 & "-APR-01"
	currentFinancialYearEnd = day(currentFinancialYearEnd) - 1  // To set last day in March.
	
Else

	currentFinancialYearStart = year(today) - 1 & "-APR-01"

	currentFinancialYearEnd = year(today) & "-APR-01"
	currentFinancialYearEnd = day(currentFinancialYearEnd) - 1  // To set last day in March.
 
Last edited:
Soldato
Joined
7 Feb 2004
Posts
8,096
Location
North East
Digging this up from the depths as it seems relevant to the discussion... I hadn't thought to post here, but this was one of the top google results!

I am relatively OK (for what I need to do) with Oracle SQL, however I'm less familiar with MS SQL and I'm struggling to come up with a query to return transactions between dates x and y, where x is 1st April 7 financial years ago and y is 31st Mar for the end of the current financial year

In Oracle, this brings back the first date of the financial year 7 years ago, taking into account that in Jan-Mar it will return 2013, or 2014 in Apr-Dec (I think those are the right years, but you get the idea!)
'01-Apr-' || (EXTRACT (YEAR FROM ADD_MONTHS (SYSDATE, -3))-7)

I have managed to get the below working, but this will return 2014 if it is run any time in 2021, which isn't quite what I want
CONCAT(YEAR(DATEADD(YY, -7, GETDATE())),'-04-01') and GETDATE()

I hope that makes sense... can anyone assist or suggest an alternative method?

I've been through the above posts, but I'm none the wiser :p ... in the meantime I'll have another read through in case something clicks!
 
Last edited:
Associate
Joined
19 Jul 2011
Posts
2,343
Thinking it thru, how many years back you go depends on whether you are before or after 1st April. So you need a case or decode statement to work that out.

Your From and To dates - are they always 1st April and today, or are they 1st april to 31st March?
 
Associate
Joined
19 Jul 2011
Posts
2,343
You probably don't need all the TO_CHAR, TO_DATE crap if you're working with native oracle date formats.
I struggle with dates unless they are DD/MM/YYYY [because thats what the app I look after generally uses]

Code:
SELECT TO_DATE(:1,'DD/MM/YYYY') DATE_TESTED
     , TO_DATE( '01/04/'||TO_CHAR(EXTRACT(YEAR from TO_DATE(:1,'DD/MM/YYYY'))),'DD/MM/YYYY' ) APRIL_THIS_YEAR,
 CASE WHEN ( TO_DATE(:1,'DD/MM/YYYY') < TO_DATE( '01/04/'||TO_CHAR(EXTRACT(YEAR from TO_DATE(:1,'DD/MM/YYYY'))),'DD/MM/YYYY' ))
      THEN 'Before'
      ELSE 'After'
  END WHERE_IN_YEAR,
 CASE WHEN ( TO_DATE(:1,'DD/MM/YYYY') < TO_DATE( '01/04/'||TO_CHAR(EXTRACT(YEAR from TO_DATE(:1,'DD/MM/YYYY'))),'DD/MM/YYYY' ))
      THEN ADD_MONTHS( TO_DATE( '01/04/'||TO_CHAR(EXTRACT(YEAR from TO_DATE(:1,'DD/MM/YYYY'))),'DD/MM/YYYY' ), (-8 * 12) )
      ELSE ADD_MONTHS( TO_DATE( '01/04/'||TO_CHAR(EXTRACT(YEAR from TO_DATE(:1,'DD/MM/YYYY'))),'DD/MM/YYYY' ), (-7 * 12) )
  END FY_BACK_SEVEN_BEGINS
FROM DUAL
;
 
Soldato
Joined
7 Feb 2004
Posts
8,096
Location
North East
Thanks @skyripper

That seems very complicated compared to how it can be calculated in OracleSQL :s , which is basically deduct 3 months from the current date, which corrects the offset, then extract the year.

I'm running through a program to import via ODBC and I've tried plugging in your formula and it doesn't seem to like it :( It's known to be finicky, but where would the table name feature in your script?

I'm trying to use the below format and I'm assuming your code is the "Y"

SELECT * FROM X WHERE Y

Thanks again :)
 
Last edited:
Associate
Joined
19 Jul 2011
Posts
2,343
The table name was DUAL, which in Oracle is a pretend table. So I guess you'd substitute in your own table name and values rather than the :1 stuff which is just bind variables. It'd also look a lot simpler when the TO_DATE and TO_CHAR function s are stripped out.
 
Associate
Joined
14 Mar 2007
Posts
1,659
Location
Winchester
Seems massively overcomplicated. In a bi environment you would just create a calendar table and join your fact table too it. No reason this shouldnt work in sql environment too. Just create csv witj primary key as start date to end date for every day. And then have flaf in the calender table specifying whether a date is in your business year. Then do an inner join in your query to the sql table and calender table.
 
Associate
Joined
24 Jun 2005
Posts
263
Thread necromancy for this:
"You probably don't need all the TO_CHAR, TO_DATE crap if you're working with native oracle date formats."
Always always *always* mask your dates (in Oracle) when writing code (as opposed to just running a query to check something).
 
Back
Top Bottom