You must Sign In to post a response.
  • Category: SQL Server

    How to get number of financial years by passing Date of purchase in sql?

    Hi fnds,

    I need to get number of financial year based on date of purchase an item.

    Ex: if DOP : 30-03-2012

    According to Indian fin year 1st Apr to 31st Mar
    the final output in terms of no. of year to till date should get @yrs=4

    30-03-2012 to 31-03-2012 - 1
    01-04-2012 to 31-03-2013 - 1
    01-04-2013 to 31-03-2014 - 1
    01-04-2014 to 15-08-2014 - 1
    ----------------------------------
    4

    Please let me know i have used below query but i am getting output is 3

    select DATEDIFF(yy, DATEADD(dd,-datediff(dd,DATEADD(yy, DATEDIFF(yy,0,'2012-03-30') , -1),'2012-03-30'),'2012-03-30'),getdate())
  • #749454
    DECLARE @InputDate DATETIME

    SET @InputDate='2012-03-30'

    SELECT CASE WHEN DATEDIFF(dd, @InputDate, CAST(YEAR(@InputDate) AS VARCHAR) +
    '-03-31')>
    0 THEN 1 ELSE 0 END + DATEDIFF(yy, @InputDate, GETDATE()) + CASE WHEN
    DATEDIFF(
    dd, DATEADD(yy, DATEDIFF(yy, @InputDate, GETDATE()), @InputDate), GETDATE
    ()) > 0
    THEN 1 ELSE 0 END AS[No of Years]


    it will helpful to you,If means mark this post.


  • Sign In to post your comments