Posted by Hugo Kornelis on 11/30/06 21:13

On 29 Nov 2006 01:28:49 -0800, Dan wrote:

>I've got a simlar problem - can you post your solution?
>Dan

Hi Dan,

The best way to solve this is to have a calendar table (see
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html),
with FiscalYear as one of it's columns.

Second best is to build a date in string format, using a format that is
guaranteed to be unabiguous WRT the order of day and month: yyyymmdd.
For isntance, for a fiscal year that starts on july first:

DECLARE @FiscalYear int
SET @FiscalYear = 2006
SELECT something
FROM sometable
WHERE TheDate >= CAST(@Year AS varchar) + '0701'
AND TheDate < CAST(@Year + 1 AS varchar) + '0701'

You might want to read this as well:
http://www.karaszi.com/SQLServer/info_datetime.asp

--
Hugo Kornelis, SQL Server MVP

[Back to original message]