Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Converting calendar date into fiscal year Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, March 25, 2014 1:45 AM
 Forum Newbie Group: General Forum Members Last Login: Thursday, April 3, 2014 2:00 AM Points: 4, 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
Post #1554307
 Posted Tuesday, March 25, 2014 3:22 AM
 SSCrazy Group: General Forum Members Last Login: Thursday, March 17, 2016 5:13 AM Points: 2,796, Visits: 2,232
 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."
Post #1554326
 Posted Tuesday, March 25, 2014 11:15 AM
 SSCrazy Group: General Forum Members Last Login: Today @ 5:10 PM Points: 2,134, Visits: 12,263
 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/
Post #1554601
 Posted Tuesday, March 25, 2014 2:15 PM This worked for the OP
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 4:50 PM Points: 23,515, Visits: 37,731
 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))`
Post #1554676
 Posted Thursday, April 3, 2014 2:03 AM
 Forum Newbie Group: General Forum Members Last Login: Thursday, April 3, 2014 2:00 AM Points: 4, 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 worksCheersAnds
Post #1557856
 Posted Thursday, April 3, 2014 2:07 AM
 Forum Newbie Group: General Forum Members Last Login: Thursday, April 3, 2014 2:00 AM Points: 4, 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!!!
Post #1557857
 Posted Thursday, April 3, 2014 2:08 AM
 Forum Newbie Group: General Forum Members Last Login: Thursday, April 3, 2014 2:00 AM Points: 4, Visits: 9
 Great thanks matey! this one works for me...
Post #1557858

 Permissions