Fiscal Year Help

[CK]

Greetings All,
I have a SQL question that maybe someone out there can help me with. Our
fiscal year starts on 01/27. I want to write a query that I can pass a date
to and it will return the week number (DATEPART("ww", someDate)) of the year
using the Fiscal Year Start Date as the base. Datepart works great except it
figures the first day of the year as 1/1. Does anyone know how I can make it
work with a first day of the year equal to the fiscal year date 01/27. Any
help would be appreciated.
TIA,
~ck

[David Portas]

"CK" <c_kett...@hotmail.com> wrote in message
news:o8Mpj.10536$EZ3....@nlpi070.nbdc.sbc.com...

Use a calendar table:

SELECT WeekNo
FROM Calendar
WHERE Dt = '20080204';

--
David Portas

[Plamen Ratchev]

You can use a calendar table. One example here:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

You can pre-calculate your week number (just mark the Fiscal Year Start Date
week as 1 and then increase every 7 days), then querying will be very
simple.

HTH,

Plamen Ratchev
http://www.SQLStudio.com