## Converting calendar date into fiscal year

 Author Message andoxcv 12409 SSC Rookie Group: General Forum Members Points: 38 Visits: 9 HiI 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 free_mascot SSCertifiable Group: General Forum Members Points: 7597 Visits: 2250 Here is the small code which will help you:DECLARE @d SMALLINTSELECT @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." pietlinden SSChampion Group: General Forum Members Points: 14294 Visits: 14152 converting calendar date into Fiscal YearThe 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/ Lynn Pettis SSC Guru Group: General Forum Members Points: 96603 Visits: 38988 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))` Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) andoxcv 12409 SSC Rookie Group: General Forum Members Points: 38 Visits: 9 HiThanks for the reply but what I did is to create a new table that will concatenate the two period eventually, and it works:-)CheersAnds andoxcv 12409 SSC Rookie Group: General Forum Members Points: 38 Visits: 9 HiThanks for the reply but what I did is to create a new table that will concatenate the two period eventually, and it works!!!;-) andoxcv 12409 SSC Rookie Group: General Forum Members Points: 38 Visits: 9 Great thanks matey! this one works for me...:-D