Post reply

Converting calendar date into fiscal year

  • March 25, 2014 at 1:45 am

    #291276

    Hi

    I am in R3 SSRS and adding a new field by converting calendar date into Fiscal Year, i.e. 2014-15.

    I used a formula but I was getting an error message, such "The arguments to the following functions are not valid: CONCAT"

    Below is my formula:

    IF(MONTH(Incident Date)<4,YEAR(Incident Date)-1&"-"&RIGHT(YEAR(Incident Date),2),YEAR(Incident Date)&"-"&RIGHT(YEAR(Incident Date)+1,2))

    What could be the issue here? Greatly appreciate your assistance, thanks!

    Andy

  • March 25, 2014 at 3:22 am

    #1699757

    Here is the small code which will help you:

    DECLARE @d SMALLINT

    SELECT @d = (SELECT Datepart("yy", Getdate()))

    SELECT Concat(@d, '-', RIGHT(@d + 1, 2))

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • March 25, 2014 at 11:15 am

    #1699991

    converting calendar date into Fiscal Year

    The most flexible way of doing this is to create a Calendar table in your database and populate it. Then you can join in your query to the Calendar table and use any of the columns there that you need.

    Here's an article that discusses it and explains how it works.

    http://www.sqlservercentral.com/articles/T-SQL/70482/

  • March 25, 2014 at 2:15 pm

    #1700056

    Here is a SQL way to do this easily:

    declare @Date date = '20140201';

    select cast(YEAR(dateadd(month,9,@Date))-1 as varchar(4)) + '-' + cast(YEAR(dateadd(month,9,@Date)) as varchar(4))

    set @Date = '20140612';

    select cast(YEAR(dateadd(month,9,@Date))-1 as varchar(4)) + '-' + cast(YEAR(dateadd(month,9,@Date)) as varchar(4))

  • April 3, 2014 at 2:03 am

    #1702757

    Hi

    Thanks for the reply but what I did is to create a new table that will concatenate the two period eventually, and it works:-)

    Cheers

    Ands

  • April 3, 2014 at 2:07 am

    #1702758

    Hi

    Thanks for the reply but what I did is to create a new table that will concatenate the two period eventually, and it works!!!;-)

  • April 3, 2014 at 2:08 am

    #1702759

    Great thanks matey! this one works for me...:-D

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply