locked
Convert date to Financial year (date) RRS feed

  • Question

  • I’ve got a date field (see below) which I want to create another column, so it get displayed as the financial year

     

    Current field

    2007-04-19 00:00:00.000

    2007-10-16 00:00:00.000

    2007-06-20 00:00:00.000

    2008-10-16 00:00:00.000

    2009-02-23 00:00:00.000

     

    The new column should show the result as

    2007/08

    2007/08

    2007/08

    2008/09

    2008/09

     

    Because I want to change the date to the financial year, the current date is from 01/04/2007 to 28/02/2009

     

    SELECT
    [ReferralDate]
    FROM
    IPReferral
    WHERE
    ReferralDate BETWEEN '20070401' AND '20090228'
    Tuesday, March 31, 2009 3:13 PM

Answers

  • Assuming your financial year starts April 1,


    UDF:

     

    IF EXISTS (SELECT name FROM sysobjects WHERE name = 'FindFy' AND type = 'fn')
    
    DROP FUNCTION FindFY
    
    GO
    
    CREATE FUNCTION dbo.FindFy (@currentdate datetime) 
    
    RETURNS varchar(9) 
    
     AS  
    
    BEGIN   
    
          
    
       declare @mo integer,
    
               @year integer,
    
               @begYr integer,
    
               @endYr integer,
    
               @fy varchar(7)
    
    
    
       SET @mo = Month(@currentdate)
    
       SET @year = Year(@currentdate)
    
    
    
       if @mo <= 3
    
       begin
    
           SET @begYr = @year - 1
    
           SET @endYr = @year 
    
       end 
    
       else
    
       begin
    
           SET @begYr = @year 
    
           SET @endYr = @year + 1
    
       end
    
    
    
       SET @fy = Cast(@begYr as varchar(4)) + '/' + RIGHT(Cast(@endYr as varchar(4)), 2)
    
            
    
       return @fy
    
    END
    
    
    
    GO
    
    

    Query:

    SELECT     dbo.FindFy(CurrentField) AS ReferralDate
    FROM        IPReferral
    WHERE     (CurrentField BETWEEN '20070401' AND '20090228')


    • Edited by DaveBurk Wednesday, April 1, 2009 12:20 PM changed where clause in query
    • Marked as answer by akhlaq768 Wednesday, April 1, 2009 1:32 PM
    Tuesday, March 31, 2009 6:31 PM

All replies



  • You can create a UDF to return your financial year (input: current field).

    What is the beginning and end of your financial year? 

    Do you want your financial year defined as '2007/08' or '2007'  and '2008' separately. Hey, the accountant in me had to ask.
    Tuesday, March 31, 2009 3:42 PM
  • i want the final output to be displayed as 2007/08

    the beginning and end of the financial year depends on the record (date) in the 'Current field' field
    Tuesday, March 31, 2009 5:10 PM

  • "the beginning and end of the financial year depends on the record (date) in the 'Current field' field"


    What are your criteria for defining your financial year?

    When you use the term financial year you have to define it.

    My company FY is  Feb.1 to Jan.31.  Therefore, my fiscal (financial) year 2009 is defined as Feb. 1, 2009 to Jan. 31, 2010.
    Tuesday, March 31, 2009 5:16 PM
  • Assuming your financial year starts April 1,


    UDF:

     

    IF EXISTS (SELECT name FROM sysobjects WHERE name = 'FindFy' AND type = 'fn')
    
    DROP FUNCTION FindFY
    
    GO
    
    CREATE FUNCTION dbo.FindFy (@currentdate datetime) 
    
    RETURNS varchar(9) 
    
     AS  
    
    BEGIN   
    
          
    
       declare @mo integer,
    
               @year integer,
    
               @begYr integer,
    
               @endYr integer,
    
               @fy varchar(7)
    
    
    
       SET @mo = Month(@currentdate)
    
       SET @year = Year(@currentdate)
    
    
    
       if @mo <= 3
    
       begin
    
           SET @begYr = @year - 1
    
           SET @endYr = @year 
    
       end 
    
       else
    
       begin
    
           SET @begYr = @year 
    
           SET @endYr = @year + 1
    
       end
    
    
    
       SET @fy = Cast(@begYr as varchar(4)) + '/' + RIGHT(Cast(@endYr as varchar(4)), 2)
    
            
    
       return @fy
    
    END
    
    
    
    GO
    
    

    Query:

    SELECT     dbo.FindFy(CurrentField) AS ReferralDate
    FROM        IPReferral
    WHERE     (CurrentField BETWEEN '20070401' AND '20090228')


    • Edited by DaveBurk Wednesday, April 1, 2009 12:20 PM changed where clause in query
    • Marked as answer by akhlaq768 Wednesday, April 1, 2009 1:32 PM
    Tuesday, March 31, 2009 6:31 PM
  • try this......


    SELECT
    left(convert(varchar(20),[ReferralDate],111),7)
    FROM
    IPReferral
    WHERE
    ReferralDate BETWEEN '20070401' AND '20090228' 

     


    http://sqlservertipsandtricks.blogspot.com
    Tuesday, March 31, 2009 6:35 PM

  • "the beginning and end of the financial year depends on the record (date) in the 'Current field' field"


    What are your criteria for defining your financial year?

    When you use the term financial year you have to define it.

    My company FY is  Feb.1 to Jan.31.  Therefore, my fiscal (financial) year 2009 is defined as Feb. 1, 2009 to Jan. 31, 2010.
    got you, its from 01/04 to 31/03 hope this helps
    Wednesday, April 1, 2009 11:35 AM
  • try this......


    SELECT
    left(convert(varchar(20),[ReferralDate],111),7)
    FROM
    IPReferral
    WHERE
    ReferralDate BETWEEN '20070401' AND '20090228' 

     


    http://sqlservertipsandtricks.blogspot.com
    you nearly got it but i want the financial year, not the month to be displayed
    Wednesday, April 1, 2009 11:41 AM
  • Try This,

    SELECT

    CAST

    (DATEPART(YY,ReferralDate) AS VARCHAR) + '/' + RIGHT((RIGHT(DATEPART(YY,ReferralDate),2) + 1),2)


    Be the change you want.
    • Marked as answer by akhlaq768 Wednesday, April 1, 2009 12:27 PM
    • Unmarked as answer by akhlaq768 Wednesday, April 1, 2009 1:11 PM
    Wednesday, April 1, 2009 12:05 PM
  • Then my proposed solution should work, the reason I put the logic in a User Defined Function is it may be useful in other queries (code re-use).


    Cheers!
    • Marked as answer by akhlaq768 Wednesday, April 1, 2009 12:25 PM
    • Unmarked as answer by akhlaq768 Wednesday, April 1, 2009 12:25 PM
    Wednesday, April 1, 2009 12:24 PM
  • perfect
    Wednesday, April 1, 2009 12:25 PM


  • Is Thilla solution what you require?


    His solution yields:

    2007/8
    2007/8
    2007/8
    2008/9
    2009/10


    Your requirements are:

    2007/08

    2007/08

    2007/08

    2008/09

    2008/09


    ***** There was a reason why I asked about your financial year *****


    Details, details, details ... it's all in the details.




    • Edited by DaveBurk Wednesday, April 1, 2009 12:35 PM Typo
    Wednesday, April 1, 2009 12:33 PM
  • you right, i get a wrong financial year, how can i correct this?
    Wednesday, April 1, 2009 1:12 PM
  • Use my solution!

    Wednesday, April 1, 2009 1:23 PM
  • cheers mate
    Wednesday, April 1, 2009 1:33 PM