January 27, 2011 at 3:20 pm
Hi all,
i have this function which is not fully working. what it does is look at the date and work out our financial year. (financial year is from 1 april to 31 march)
for example for financial year 2010-04-01 to 2010-03-01
I would like to show
2010/11 2010-04-01
2010/11 2010-05-01
.
.
.
.
2010/11 2011-01-01
2010/11 2011-02-01
2010/11 2011-03-01
the function i use is this-
ALTER function [dbo].[fFinYear] (@FM char(6))
returns char(7)
as
begin
return
left(@FM,4)+
case
when substring(@FM,3,2)='99' then '/00'
when substring(@FM,3,2)+1<10 then '/0'+rtrim(convert(varchar(2),substring(@FM,3,2)+1))
else '/'+rtrim(convert(varchar(2),substring(@FM,3,2)+1))
end
end
what this does is
2010/11 2010-04-01
2010/11 2010-05-01
.
.
.
.
2011/12 2011-01-01
2011/12 2011-02-01
2011/12 2011-03-01
I would like it to stay 2010/11 to end of march and once it reaches april then increase to 2011/12. I would be ever greatful if someone can help me on this?
January 27, 2011 at 4:23 pm
Probably the easiest way to deal with fiscal years not following the calendar year would be using a calendar table where you'd add the FY information per day.
You could also use such a table to store working day as well as holiday information.
Usually quite useful.
January 27, 2011 at 5:57 pm
jbon007 (1/27/2011)
Hi all,i have this function which is not fully working. what it does is look at the date and work out our financial year. (financial year is from 1 april to 31 march)
for example for financial year 2010-04-01 to 2010-03-01
I would like to show
2010/11 2010-04-01
2010/11 2010-05-01
.
.
.
.
2010/11 2011-01-01
2010/11 2011-02-01
2010/11 2011-03-01
the function i use is this-
ALTER function [dbo].[fFinYear] (@FM char(6))
returns char(7)
as
begin
return
left(@FM,4)+
case
when substring(@FM,3,2)='99' then '/00'
when substring(@FM,3,2)+1<10 then '/0'+rtrim(convert(varchar(2),substring(@FM,3,2)+1))
else '/'+rtrim(convert(varchar(2),substring(@FM,3,2)+1))
end
end
what this does is
2010/11 2010-04-01
2010/11 2010-05-01
.
.
.
.
2011/12 2011-01-01
2011/12 2011-02-01
2011/12 2011-03-01
I would like it to stay 2010/11 to end of march and once it reaches april then increase to 2011/12. I would be ever greatful if someone can help me on this?
Parapharsing here, the new fiscal year (2011/12, now called 2012 for my purposes) start on 2011-04-01 an ends on 2012-03-31, correct?
January 27, 2011 at 6:03 pm
Does this help you get started?
declare @TestDate date;
set @TestDate = '20110401';
select @TestDate, DATEADD(mm,9,@TestDate), CAST(year(DATEADD(mm,9,@TestDate)) - 1 as VARCHAR) + '/' + RIGHT(CAST(year(DATEADD(mm,9,@TestDate)) as VARCHAR),2);
set @TestDate = '20120331';
select @TestDate, DATEADD(mm,9,@TestDate), CAST(year(DATEADD(mm,9,@TestDate)) - 1 as VARCHAR) + '/' + RIGHT(CAST(year(DATEADD(mm,9,@TestDate)) as VARCHAR),2);
January 28, 2011 at 1:11 am
hi lynn,
thats right for fiscal year 01/04/2010 to 31/03/2011 would be 2010/11
January 28, 2011 at 3:08 pm
Finally got it working. Many thanks for all of you to providing help.
i've posted the code if anyone else comes to the same problem.
USE [ColossusUser]
GO
/****** Object: UserDefinedFunction [dbo].[fFinYear] Script Date: 01/28/2011 22:05:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fFinYear] (@v_date datetime)
RETURNS varchar(7) AS
BEGIN
DECLARE @v_year as VARCHAR(7)
IF MONTH(@v_date) > 3
BEGIN
SET @v_year = CAST(year(@v_date) as varchar) + '/' + Right(CAST((year(@v_date)) +1 as varchar),2)
END
IF MONTH(@v_date) between 0 and 3
BEGIN
SET @v_year = CAST(year(@v_date) -1 as varchar) + '/' + Right(CAST((year(@v_date) -1) +1 as varchar),2)
END
RETURN @v_year
END
January 28, 2011 at 5:09 pm
Two more options. Option 2 allows you to use the function in a CROSS APPLY in a query instead of using the Scalar UDF in the select part of a query. You will find that the cross apply is faster.
DROP FUNCTION dbo.fFinYear;
GO
CREATE FUNCTION [dbo].[fFinYear] (@v_date datetime)
RETURNS varchar(7) AS
BEGIN
return CAST(year(DATEADD(mm,9,@v_date)) - 1 as VARCHAR) + '/' + RIGHT(CAST(year(DATEADD(mm,9,@v_date)) as VARCHAR),2);
END;
GO
select dbo.fFinYear('20110128');
GO
DROP FUNCTION dbo.fFinYear;
GO
CREATE FUNCTION [dbo].[fFinYear] (@v_date datetime)
RETURNS table
AS
return select CAST(year(DATEADD(mm,9,@v_date)) - 1 as VARCHAR) + '/' + RIGHT(CAST(year(DATEADD(mm,9,@v_date)) as VARCHAR),2) as FiscalYear;
go
select * from dbo.fFinYear('20110128');
go
drop function dbo.fFinYear;
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply