Deriving financial years

Question

Post reply

Deriving financial years

Mark Green

SSCrazy

Points: 2085
More actions
July 11, 2006 at 11:35 am

#113685

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.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

And those financial fiscal year intervals are..?

N 56°04'39.16"
E 12°55'05.25"

Mark Green SSCrazy Points: 2085 More actions · Answer 2

The financial year in the uk runs from the 1st of April to the 31st of March.

Mark Green SSCrazy Points: 2085 More actions · Answer 3

I wish that SQL had a function that would allow me to do something like:

SELECT "Financial Year Number" = FINANCIALYEAR('03/12/1998')GO

Bagath SSCrazy Points: 2409 More actions · Answer 4

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.

RyanRandall SSChampion Points: 13623 More actions · Answer 5

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.

Mark Green SSCrazy Points: 2085 More actions · Answer 6

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?

RyanRandall SSChampion Points: 13623 More actions · Answer 7

> 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.

Mark Green SSCrazy Points: 2085 More actions · Answer 8

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.

RyanRandall SSChampion Points: 13623 More actions · Answer 9

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.

stax68 SSChampion Points: 11711 More actions · Answer 10

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

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)

RyanRandall SSChampion Points: 13623 More actions · Answer 12

> 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.

stax68 SSChampion Points: 11711 More actions · Answer 13

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

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)