Love this util. But I had a major issue with it. Being outside the US of A, I don't use MM/dd/YYYY format dates. Because I run into a lot of US dates format/converting problems I set all my SQL servers to use ISO like date format, yyyy-MM-dd. Also avoids user confusion with reports..
I found that your util can create invalid constraints on the slide out tables (and presumable will do the same on the staging) as it inserts the constraints in American style formatted dates. This causes the util to create a slide out table that can't have partition data switched to. I've updated the CreateStgPartitionCheck() in the source code so that if the partition column is SqlDateType.DateTime (The only sql data type I use for partitioning..) then it will properly format the string and works ok for me now.
So now the construct for the min value predicate looks a little like this:
// Construct the minimum value predicate string in the check constraint definition
if (partitionNumber > 1)
// 2010-01-22 Added By Yussuf Khan so it can cope with non US format DateTime formats (I don't use other datatypes for my partition columns so only coding datetime)
if (partitionColumnType == SqlDataType.DateTime)
//now it seems for datetime it will return this in US format, dd/MM/yyyy
//need to convert to more universal standard, yyyy-MM-dd , which SQL server detects ok no matter what the locale (well in my experience anyways!)
dtDateBuilder = (DateTime)pf.RangeValues[partitionNumber - 2];
strDateBuilder = dtDateBuilder.ToString("yyyy-MM-dd hh:mm:ss.fff");
leftBoundary = partitionColumnName + ((pf.RangeType == RangeType.Right) ? ">=" : ">") + "N'" + strDateBuilder + "'";
else if (
(partitionColumnType == SqlDataType.Date) ||
(partitionColumnType == SqlDataType.Time) ||
I've done a similiar thing to the Max value string constraint builder too. I'm not really a .Net developer, and I've only tested a couple of times. But this does seem to fix. Any chance you can incorporate something like this fully?