2

Closed

Invalid Constraints when server not using US format dates

description

Hello,
 
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?
Closed Nov 29, 2012 at 4:46 AM by Stuarto
Fixed in Version 3.0

comments

Stuarto wrote Jan 27, 2010 at 4:20 PM

Thank you for the feedback. I was surprised to see that the pf.RangeValue was not already formatted in the locale-specific formulation. I will incorporate this change in a subsequent build after testing more completely.