Why partition function works for datetime2 but not for datetime1

• mssqlnoob

  Ten Centuries

  Points: 1245

  More actions

  December 20, 2013 at 1:57 am

  #301629

  Hi all,

  DECLARE @DatePartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION DatePartitionFunction (datetime) AS RANGE RIGHT FOR VALUES (';

  DECLARE @i datetime = '2007-09-01 00:00:00.000';

  WHILE @i < '2008-10-01 00:00:00.000'

  BEGIN

  SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(10)) + '''' + N', ';

  SET @i = DATEADD(MM, 1, @i);

  END

  SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(10))+ '''' + N');';

  EXEC sp_executesql @DatePartitionFunction;

  GO

  Msg 7705, Level 16, State 2, Line 1

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

  however if I change to datetime2 it works

  DECLARE @DatePartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION DatePartitionFunction (datetime2) AS RANGE RIGHT FOR VALUES (';

  DECLARE @i datetime2 = '2007-09-01 00:00:00.000';

  WHILE @i < '2008-10-01 00:00:00.000'

  BEGIN

  SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(10)) + '''' + N', ';

  SET @i = DATEADD(MM, 1, @i);

  END

  SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(10))+ '''' + N');';

  EXEC sp_executesql @DatePartitionFunction;

  GO

  Command(s) completed successfully.

  with reference to http://technet.microsoft.com/en-us/library/ms187802.aspx

  input_parameter_type

  Is the data type of the column used for partitioning. All data types are valid for use as partitioning columns, except text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), alias data types, or CLR user-defined data types.

  in this case why isn't datetime works?

  version is as follow:

  Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)

  Dec 28 2012 20:23:12

  Copyright (c) Microsoft Corporation

  Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

  from http://msdn.microsoft.com/en-us/library/cc645993.aspx

  Table and index partitioning is supported in this edition

  so I don't know why it fails!

  thanks a lot!

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  December 20, 2013 at 2:12 am

  #1675068

  If you change the sp_executesql to a PRINT, you should see exactly why it's not working.

  What your code generates is this:

  CREATE PARTITION FUNCTION DatePartitionFunction (DATETIME) AS RANGE RIGHT FOR

  VALUES ('Sep 1 200', 'Oct 1 200', 'Nov 1 200', 'Dec 1 200', 'Jan 1 200', 'Feb 1 200', 'Mar 1 200', 'Apr 1 200', 'May 1 200', 'Jun 1 200', 'Jul 1 200', 'Aug 1 200', 'Sep 1 200', 'Oct 1 200');

  Hint, you need a CONVERT with a format code instead of CAST(@i as nvarchar(10)).

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass