datepart problem with week extraction (T-SQL)

I'm using datepart combined with a count aggregate to count the number of
ws in a certain time period. Problem is, my employer starts each w on
Saturday. The T-SQL version of Datepart does not support a StartOfW
parameter. This defect is screwing up my reports.
Does anyone have a workaround?
Thanks,
Randall ArnoldLook up SET DATEFIRST in BOL to control the first day of the w for date
functions
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:e$odFORVGHA.2704@.tk2msftngp13.phx.gbl...
> I'm using datepart combined with a count aggregate to count the number of
> ws in a certain time period. Problem is, my employer starts each w
> on Saturday. The T-SQL version of Datepart does not support a StartOfW
> parameter. This defect is screwing up my reports.
> Does anyone have a workaround?
> Thanks,
> Randall Arnold
>|||Another alternative is to use a calendar table, whcih gives you the
flexibility of using multiple calendars associated with the same date.
Stu|||But be aware that SQL Server doesn't calculate w number the way that the
majority of the world
does.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dave Frommer" <anti@.spam.com> wrote in message news:uG7g6vSVGHA.736@.TK2MSFTNGP12.phx.gbl..
.
> Look up SET DATEFIRST in BOL to control the first day of the w for date
functions
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:e$odFORVGHA.2704@.tk2msftngp13.phx.gbl...
>|||Apparently not.
First, I tried setting Datefirst in a view, and got a syntax error. Using
the exact same SQL in a stored procedure didn't result in an error, but it
had no effect, either. No matter what value I set Datefirst to, the w is
still calculated wrong. For my purposes, 4/2/2005 needs to show as w 15
(first day of w = Saturday), but it always shows as w 14.
I don't see how a date lookup table will solve this... so, any other ideas?
This shortcoming in T-SQL is producing invalid results in my datasets...
Randall Arnold
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%235j4iOWVGHA.5012@.TK2MSFTNGP10.phx.gbl...
> But be aware that SQL Server doesn't calculate w number the way that
> the majority of the world does.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dave Frommer" <anti@.spam.com> wrote in message
> news:uG7g6vSVGHA.736@.TK2MSFTNGP12.phx.gbl...
>|||> I don't see how a date lookup table will solve this...
You have a table with one row per day. One of the columns in this table is t
he w correct number.
Or, read in Books Online about the ISOW function, install and use that in
stead of DATEPART().
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:OoTFjvyVGHA.5592@.TK2MSFTNGP09.phx.gbl...
> Apparently not.
> First, I tried setting Datefirst in a view, and got a syntax error. Using
the exact same SQL in a
> stored procedure didn't result in an error, but it had no effect, either.
No matter what value I
> set Datefirst to, the w is still calculated wrong. For my purposes, 4/
2/2005 needs to show as
> w 15 (first day of w = Saturday), but it always shows as w 14.
> I don't see how a date lookup table will solve this... so, any other ideas
? This shortcoming in
> T-SQL is producing invalid results in my datasets...
> Randall Arnold
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%235j4iOWVGHA.5012@.TK2MSFTNGP10.phx.gbl...
>|||Ok, thanks Tibor. I should have realized how the date (w) lookup would
work (I've done a similar thing for Periods), my bad. I'm just already
linking to so many tables on this query it's become a nightmare (due to bad
database design by the original dba). I'll get familiar with with ISOWEEK
and if that doesn't work, I have a Period lookup table already and I'll just
add a w column to it.
Randall Arnold
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6BX%230yVGHA.4960@.TK2MSFTNGP12.phx.gbl...
> You have a table with one row per day. One of the columns in this table is
> the w correct number.
> Or, read in Books Online about the ISOW function, install and use that
> instead of DATEPART().
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:OoTFjvyVGHA.5592@.TK2MSFTNGP09.phx.gbl...
>|||I decided just to add the W column to my existing Period table. It meant
changing several queries, as well as more manual maintenance, but it works.
Thanks again.
Randall Arnold
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6BX%230yVGHA.4960@.TK2MSFTNGP12.phx.gbl...
> You have a table with one row per day. One of the columns in this table is
> the w correct number.
> Or, read in Books Online about the ISOW function, install and use that
> instead of DATEPART().
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:OoTFjvyVGHA.5592@.TK2MSFTNGP09.phx.gbl...
>