SQL Dates - Financial Year

Ciphon · 10 Sep 2010 at 18:36

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

Redgie · 10 Sep 2010 at 19:17

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 ^^

Ciphon · 10 Sep 2010 at 19:21

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()))))

MFrost · 10 Sep 2010 at 19:22

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

).

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.

Ciphon · 10 Sep 2010 at 19:33

MFrost said:
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 · 11 Sep 2010 at 09:31

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

Conficker · 24 Feb 2012 at 10:22

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

djkav · 24 Feb 2012 at 11:15

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.

jellybeard999 · 11 Jun 2021 at 10:32

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

... in the meantime I'll have another read through in case something clicks!

skyripper · 11 Jun 2021 at 14:28

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?

skyripper · 11 Jun 2021 at 14:46

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
;

skyripper · 11 Jun 2021 at 14:48

So if you feed it 01/03/2021, it will give you 01-Apr-2013.
If you feed it 01/06/2021, it will give you 01-Apr-2014

@jellybeard999

jellybeard999 · 15 Jun 2021 at 10:50

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

touch · 15 Jun 2021 at 12:31

How about this:

CAST(CONCAT(DATEPART(year,DATEADD(month, -3, GETDATE()))-7,'-04-01')as DateTime)

jellybeard999 · 16 Jun 2021 at 15:44

touch said:
How about this:

CAST(CONCAT(DATEPART(year,DATEADD(month, -3, GETDATE()))-7,'-04-01')as DateTime)

@touch

That's exactly what I was after! Thanks

skyripper · 16 Jun 2021 at 22:24

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.

skyripper · 16 Jun 2021 at 22:27

However touch's all in one function looks like a damn good bet too!

Dolph · 2 Jul 2021 at 22:41

It's potentially worth exploring the idea of a date dimension of you have any influence on the design of the database/data warehouse.

https://radacad.com/do-you-need-a-date-dimension

Implemented well, it makes it very easy to handle all sorts of weird business date requirements without the user having to work out how to make SQL do it themselves.

dazzerd · 10 Jul 2021 at 09:10

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.

Ergates · 6 Sep 2021 at 13:30

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).