Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Converting calendar date into fiscal year Expand / Collapse
Author
Message
Posted Tuesday, March 25, 2014 1:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 3, 2014 2:00 AM
Points: 4, Visits: 9
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
Post #1554307
Posted Tuesday, March 25, 2014 3:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1554326
Posted Tuesday, March 25, 2014 11:15 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 745, Visits: 4,748
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/
Post #1554601
Posted Tuesday, March 25, 2014 2:15 PM This worked for the OP Answer marked as solution


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:33 PM
Points: 23,277, Visits: 31,997
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 Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1554676
Posted Thursday, April 3, 2014 2:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 3, 2014 2:00 AM
Points: 4, Visits: 9
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
Post #1557856
Posted Thursday, April 3, 2014 2:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 3, 2014 2:00 AM
Points: 4, Visits: 9
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!!!
Post #1557857
Posted Thursday, April 3, 2014 2:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse