Changing "Firstweekofyear" within a query

[Guest]

First, I apologize for what may appear as a cross-post. I inadvertantly
posted this question to another post that is 2 years old. (I don't know how I
missed that)

Is it possible to alter the "starting week" for the year to reflect a fiscal
calendar instead of a standard calendar?
The code that I am using looks like this:
SELECT DatePart("ww",[sentdate],1) AS Week, Count(tbl_mytable.sentdate) AS
CountOfnum
FROM tbl_mytable
GROUP BY DatePart("ww",[sentdate],1);

I know that if I do not specify the constant for the <firstweekofyear>
argument that it defaults to the week that has January 1st.
Is there a way to have the function start at say October 1st instead? This
would synchronize the week number count that I have as a result with the
fiscal calendar that we use.
TIA

 

[Douglas J. Steele]

No, there's no capability like that. You can use DateAdd to change the date
appropriately before calculating the quarter.

 

[Guest]

Try these --
Fiscal Year: DatePart("yyyy",DateAdd("m",3,[sentdate]))
Week of Fiscal Year: DatePart("ww",DateAdd("m",3,[sentdate]))

 

[Guest]

Karl,
This code works perfectly! What I discovered is that the Fiscal Year code
gives a complete total for the period, and that the Week of Fiscal Year code
is exactly what I was hoping to find.
Thanks ever so much for your assistance!

Try these --
Fiscal Year: DatePart("yyyy",DateAdd("m",3,[sentdate]))
Week of Fiscal Year: DatePart("ww",DateAdd("m",3,[sentdate]))
--
KARL DEWEY
Build a little - Test a little

First, I apologize for what may appear as a cross-post. I inadvertantly
posted this question to another post that is 2 years old. (I don't know how I
missed that)

Is it possible to alter the "starting week" for the year to reflect a fiscal
calendar instead of a standard calendar?
The code that I am using looks like this:
SELECT DatePart("ww",[sentdate],1) AS Week, Count(tbl_mytable.sentdate) AS
CountOfnum
FROM tbl_mytable
GROUP BY DatePart("ww",[sentdate],1);

I know that if I do not specify the constant for the <firstweekofyear>
argument that it defaults to the week that has January 1st.
Is there a way to have the function start at say October 1st instead? This
would synchronize the week number count that I have as a result with the
fiscal calendar that we use.
TIA