SQL Calculation for ASP.NET

Question

In my SQL database I have a column that contains a fiscal year value. Fiscal Year start from Oct 1st to Sept 30th of the following year. For example the current fiscal year is 2011-2012 or "2011". I need a calculation to pull dates from my database.

I have a column in my table that contains dates (i.e. 05/04/2012), I need a calculation that will pull the dates for the selected fiscal year? So when I need to see the data for the date 02/14/2003, then I would need the fiscal year of 2002.

This all ties into my ASP.NET page, where a user selects which fiscal year they want to view and a gridview appears with the information requested. So, if I choose fiscal year 2010, the data pulled into the gridview should be all records from 10/01/2010 to 09/30/2011. Thanks for the help, I have yet to try anything because I am not sure where to begin (sql newbie).

Andomar · Accepted Answer · 2012-06-13 12:13:45Z

3

You can find the fiscal year by adding three months:

year(dateadd(month,3,'2011-09-30'))

So to select all entries in Fiscal year 2011, try:

select  *
from    YourTable as yt
where   year(dateadd(month,3,yt.TransactionDt)) = 2011

answered Jun 13, 2012 at 12:13

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tisho · Accepted Answer · 2012-06-13 12:17:26Z

1

I assume you are using SqlParameters to send data to the SQL Server. Then:

int fiscal_year = 2002;

SqlParameter fyStart = new SqlParameter("@FiscalYearStart", 
    SqlDbType.DateTime);
fyStart.Value = new SqlDateTime(fiscalYear, 10, 01);

SqlParameter fyEnd = new SqlParameter("@FiscalYearEnd", 
    SqlDbType.DateTime);
fyEnd.Value = new SqlDateTime(fiscalYear+1, 10, 01);

Then you can pass these two params to an Stored Procedure for example, and to query the table with

WHERE date BETWEEN @FiscalYearStart AND @FiscalYearEnd

N.B. FiscalYearEnd should be 10-Oct-YEAR+1, as it will be represented as YYYY-10-01T00:00:00, or will include the whole 30 Sept.

answered Jun 13, 2012 at 12:17

Tisho

8,5426 gold badges46 silver badges52 bronze badges

Comments

Dave Markle · Accepted Answer · 2012-06-13 12:18:06Z

You could query for it:

SELECT *
FROM YourTable
WHERE YourTableDate >= CAST(@FiscalYear AS CHAR(4)) + '-10-01'
AND   YourTableDate <  CAST(@FiscalYear + 1 AS CHAR(4)) + '-10-01';

or, if you need the flexibility, you could alternatively have a table of date ranges which join to fiscal years. This gives you the ability to have multiple fiscal year definitions for, say, multiple tenants or companies, or allows the definition to change without changing your queries. You could then join to this table as needed to filter your results.

CompanyID FiscalYear StartDate  EndDate
1         2010       2010-10-01 2011-09-30
1         2011       2011-10-01 2012-09-30
2         2010       2010-01-01 2011-12-31
2         2011       2011-01-01 2012-12-31


SELECT *
FROM YourTable t
INNER JOIN FiscalYear y
    ON y.FiscalYear = t.FiscalYear
WHERE t.YourTableDate >= y.StartDate AND t.YourTableDate < DATEADD(d, 1, y.EndDate);

Collectives™ on Stack Overflow

SQL Calculation for ASP.NET

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related