financial year

how can you store a financial year in sql 2005

wt do you mean by that? can u explain it further so that we can get the exact idea wt u wanna do?|||

What i ment is that when a person selects a date how do you set another column just to show the financial year....

EG:

column 1 = '09/09/2004'

column 2(financial Year) = '2005'

what i have done is that i have created a case satement that selects the correct date

THANKS

|||

You can do it in a select statement:

SELECT tDate, CASE WHEN MONTH(tDate)>6 then YEAR(tDate)+1

ELSE YEAR(tDate) END AS FYear

FROM Dates

Or you can save the value in a new column such as a INT column through an update statement:

UPDATE Dates

SET fYear = CASE WHEN MONTH(tDate)>6 then YEAR(tDate)+1

ELSE YEAR(tDate) END

|||

I would absolutely suggest a calendar table like this for your translation needs. You calculate it once, store it away and then reference it. Here is an article (with code) I wrote on how to load a calendar table, including columns for fiscal_year and month:

A way to load a calendar table
http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1349.entry

It also has several references to other articles.