Posted by Erland Sommarskog on 02/20/07 22:47

(eavery@cdc.gov) writes:
> I can't seem to find a way to do the following:
>
> create table part_table (
> col1 int,
> col2 datetime
> ) on psX (datename(week,col2))
>
> I want to partition based on the week number of a date field.
>
> So if I enter in data like the following in my part_table:
>
> (1, 1/1/2007) should go into partition 1 for week #1
> (52, 12/21/2007) should go into partition 52 for week #52 of the year
>
> I tried adding in a computed column, but it says its nondeterministic.

You are out of luck, I'm afraid. datename() is listed as entirely
undeterministic in Books Online. The normal choice would be datepart(),
which Books Online says is deterministic - with two exceptions of one
being the week number. This example shows why:

set datefirst 7
go
select datepart(week, '20070218') -- 8
go
set datefirst 1
go
select datepart(week, '20070218') -- 7
go


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]