Deriving financial years
-
I have a date in the form of:
2006-02-26 00:00:00.000
At the moment I process this date like so:
CONVERT(VARCHAR(4), EpiStartDate)
And this gives me the year. But now I wish to do something a bit more complex than that.
I wish to assess which financial year the date belongs to. And then label that with the lowest year in the financial year. For example, if a date falls in the financial year 2004/2005 I wish to label it 2004. Can anyone please help me with that? I am working with uk financial years by the way.
-
And those financial fiscal year intervals are..?
N 56°04'39.16"
E 12°55'05.25" -
The financial year in the uk runs from the 1st of April to the 31st of March.
-
I wish that SQL had a function that would allow me to do something like:SELECT "Financial Year Number" = FINANCIALYEAR('03/12/1998')GO -
Hello,
I have no idea about the financialyear function...
But tried as below...check it out...
ALTER PROCEDURE Financial_Year @Enter_Date DATETIME=NULL
AS
DECLARE @Mt INT
DECLARE @Yr INT
BEGIN
SET @Mt=0
SET @Yr=0
IF @Enter_Date IS NULL
SELECT @Enter_Date=GETDATE()
SELECT @Mt=CONVERT(INT,DATEPART(M,@Enter_Date))
IF @Mt<4
BEGIN
SELECT @Yr=CONVERT(INT,DATEPART(YY,@Enter_Date))-1
PRINT 'The Financial Year Is'+' '+CAST( @Yr AS VARCHAR(100))
END
ELSE
BEGIN
SELECT @Yr=CONVERT(INT,DATEPART(YY,@Enter_Date))
PRINT 'The Financial Year Is'+' '+CAST( @Yr AS VARCHAR(100))
END
PRINT 'Thank You For Using Our Service'
END
----------------------------------------
Exec Financial_Year --Default value is today's date
or
Exec Financial_Year '2005-03-01 00:00:00.000'
Thanks,
Rao Aregaddan.
-
Goodness. This might be a bit simpler...
select d, year(d + 275)-1 from YourTable
If you need a function, you can use...
create function dbo.FINANCIALYEAR(@d datetime) returns int as begin return year(@d + 275)-1 end
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
I am sure that this solution is on the right lines. How do I set the start and end date for the financial year? Also, can I create a function within a SPROC or does it permanently reside separately in the database?
-
> Also, can I create a function within a SPROC or does it permanently reside separately in the database?
It permanently resides separately in the database.
> How do I set the start and end date for the financial year?
What do you mean? This kind of thing?
--data
declare @t table (d datetime)
insert @t
select '20060226'
union all select '19981203'
union all select '20060331'
union all select '20060401'
--calculation
select
d,
year(d + 275)-1 as FINANCIALYEAR,
dateadd(year, year(d + 275)-1901, '19000401') as FINANCIALYEARSTART,
dateadd(year, year(d + 275)-1900, '19000331') as FINANCIALYEAREND
from @t
/*results
d FINANCIALYEAR FINANCIALYEARSTART FINANCIALYEAREND
-------------------------------------- ---------------------------------------------------
2006-02-26 00:00:00.000 2005 2005-04-01 00:00:00.000 2006-03-31 00:00:00.000
1998-12-03 00:00:00.000 1998 1998-04-01 00:00:00.000 1999-03-31 00:00:00.000
2006-03-31 00:00:00.000 2005 2005-04-01 00:00:00.000 2006-03-31 00:00:00.000
2006-04-01 00:00:00.000 2006 2006-04-01 00:00:00.000 2007-03-31 00:00:00.000
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
I think that we are going down the wrong path with this. I have decided to create a tempory lookup table for the solution. But your posts have been inteteresting and I will return to the idea of creating my own functions at some stage in the future. Thanks.
-
Okay. It's hard for me to tell whether we're going down the wrong path or not, since I don't have enough information to understand what you're trying to do. So I'll have to take your word for it.
If you're thinking about creating a temporary date lookup table, this function may help you (and should also be interesting)...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
In case you wander back onto this path, this might be what you were after:
- create
function dbo.getFY(@date datetime, @FYstartmonth tinyint, @FYstartday tinyint)
- returns
smallint
- as
- begin
- return year(dateadd(day,1-@FYstartday,dateadd(month,1-@FYstartmonth,@date)))
- end
- go
- declare
@D table(d datetime)
- declare
@FYstartmonth int, @FYstartday int, @i int
- select @i = 1
- while
@i < 366
- begin
- insert @D select @i + 38715
- select @i = @i + 1
- end
- select
d, dbo.getFY(d,4,1) fy from @D
- go
- drop function dbo.getFY
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
I guess I don't understand why no-one liked Ryan's first solution with the -275 thingy (although I'm not sure it will withstand a Leap Year). It was very simple and a simple calculation like that is most assuredly faster than any join to a temp table.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
> I'm not sure it will withstand a Leap Year
How dare you Jeff!
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
You could at least read a post before you include it in blanket criticism. There's no temp table in my solution. That's for test data. The counterpart of Ryan's code using my method is:
- select year(dateadd(day,1-@FYstartday,dateadd(month,1-@FYstartmonth,@date)))
the two extra parameters are for configuring the start date of the financial year - a refinement which was specifically requested. If you don't want the configuration parameters, the solution becomes:
select year(dateadd(month,-3,@date))
which has the distinct benefit of handling leap years as well as not relying on the (unsupported?) integer/date mapping.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Sorry, Tim... poor choice of words on my part.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply