How do I get financial year date range

Question

2.33/5 (2 votes)

See more:

SQL-Server-2008R2

Hi,

I have tow dates startDate and EndDate. Depending on this date range I need to find financial year start date and end date for the same date range.

I need an sql query for the same.

i tried like:

SQL

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

but i guess it will give me current financial year..
Please help me out.

Posted 4-Jul-14 0:36am

comred

Updated 18-Jan-20 1:19am

v2

Add a Solution

Comments

CHill60 4-Jul-14 6:40am

What have you tried so far?
What is your financial year start date?
What if your two dates fall into two financial years?

coded007 4-Jul-14 6:43am

Do you have any tables specifying financial years?

comred 4-Jul-14 7:45am

No i don't

2 solutions

Solution 1

You will have to code this for yourself: there is no such thing as a "universal" financial year, and each country has a different one: http://en.wikipedia.org/wiki/Fiscal_year[^]

So you will have to work out what country you want, and move to the appropriate range yourself.

Posted 4-Jul-14 0:46am

OriginalGriff

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Magic Wonder · Accepted Answer · 2014-07-04T01:03:00

Hi,

As per solution 1, fiscal year is different for different countries. Here i can give you little example but still you have to do final work out as per your requirement.

Here GETDATE() May Replace "Your Date"

SQL

SELECT QM_FIN_YEAR =
        CASE
            WHEN Month(GETDATE()) BETWEEN 4 AND 12
                THEN CONVERT(VARCHAR(4),YEAR(GETDATE())) + '-' + CONVERT(VARCHAR(4),YEAR(GETDATE()) + 1)
            WHEN Month(GETDATE()) BETWEEN 1 AND 3
                THEN CONVERT(VARCHAR(4),YEAR(GETDATE()) - 1) + '-' + CONVERT(VARCHAR(4),YEAR(GETDATE()) )
End ORDER by 1 DESC

Hope this will help you.