0

I have a large table that I want to partition by a datetime2(0) column. AFAIK, datetime2(0) is stored as 'YYYY-MM-DD hh:mm:ss'. However, I can't create the partition function with the same format.

Use PartitionLogTest
Go
CREATE PARTITION FUNCTION pfRequestTimeRange (DATETIME2(0))
AS
RANGE RIGHT FOR VALUES (NULL,
                        '2015-01-01 00:00:00', 
                        '2016-01-01 00:00:00',
                        '2017-01-01 00:00:00',
                        '2018-01-01 00:00:00')

Error message:

Could not implicitly convert range values type specified at ordinal 1 to partition function parameter type.

I've tried formats like '20150101000000' or '2015-01-01 00:00:00.000' but still couldn't make it work. Is there a correct format or I can't/shouldn't create partition function with datetime2(0)?

1
  • 2
    You should explicitly use CONVERT() with a specific date format rather than relying on implicit conversions.
    – AMtwo
    Apr 28, 2017 at 13:19

1 Answer 1

1

Removing the NULL as the first partition value should get you past the error. (UPDATE - Read the comments further down from Dan's post - he actually DOES recommend the action of the OP - you just need to use convert(datetime2(0),null)

Also - check out Dan Guzman's Table Partitioning Best Practices.

Well - after re-reading Dan's post, he actually DOES recommend creating the first partition with a NULL boundary for RANGE RIGHT,

I suggest specifying a NULL value for the first boundary of a RANGE RIGHT partition function. This NULL boundary serves as the upper boundary of the permanent first partition as well as the lower boundary for the second partition containing data outside the expected range. No rows are less than NULL so the first partition will always be empty. It is therefore safe to map the first partition to the previously mentioned dummy filegroup even if you need to house data outside the expected range. That being said, there is no harm in mapping the first partition to another filegroup other than lack of clarity.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.