Click here to Skip to main content
14,644,142 members
Rate this:
Please Sign up or sign in to vote.
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:

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
Updated 18-Jan-20 1:19am
v2
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
Rate this:
Please Sign up or sign in to vote.

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.
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

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"

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.
   
v2
Comments
comred 4-Jul-14 7:55am
   
It is helpful.. Thanks Magic.. It works. At least it gives me a hint to what shld I do.
Magic Wonder 4-Jul-14 8:57am
   
Yup,...Your welcome.
Emil Georg 22-May-18 3:31am
   
It's works perfectly as I required. Thanks.
Magic Wonder 23-May-18 1:25am
   
Welcome

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100