CSQL SERVER Partition Function-ould not implicitly convert range values type specified at ordinal 1 to partition function parameter type.

NickK 156 Reputation points
2022-06-11T12:36:39.617+00:00

HI Experts, can someone help with why I am getting this error? it works when I use varchar(500) but MY index creation fails as the Table column has date datatype

CREATE PARTITION FUNCTION DatePartition (Date)
AS RANGE Right FOR VALUES
('2022–03–01',
'2022–04–01',
'2022–04–14',
'2022–05–01',
'2022–05–14',
'2022–06-01',
'2022–06–10',
'2022–07-01',
'2022–07–14',
'2022–08-01'
)
Msg 7705, Level 16, State 2, Line 15
Could not implicitly convert range values type specified at ordinal 1 to partition function parameter type.
I read so many articles and followed a few examples but no luck for me
https://www.sqlservertutorial.net/sql-server-administration/sql-server-partition-existing-table/
Please suggest so I can move from this error

-----i have not pasted PartitionScheme here

CREATE CLUSTERED INDEX IX__PROJECTION_ReportDate ON PROJECTION
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON PartitionScheme(ReportDate)
GO

--Msg 7726, Level 16, State 1, Line 242
--Partition column 'ReportDate' has data type date which is different from the partition function 'DatePartitions' parameter data type varchar(500).

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,597 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 83,101 Reputation points
    2022-06-11T13:36:16.697+00:00

    To fix the first problem, try replacing all of “–” (special dash) with an ordinary “-”. (Retype it manually).

    No comments

  2. Bert Zhou-msft 3,386 Reputation points
    2022-06-13T01:51:21.547+00:00

    Hi,@NickK

    I don't know if your partition function creation is wrong because you imported it to the forum , I agree with viole l, after you manually modify the '-' , the first problem is solved.

    The second error has obviously prompted the problem . When you are going to create a partition scheme index on the table , of course , the parameter data type and the number of partitions of the partition function must be kept the same .
    Like this :

    CREATE PARTITION FUNCTION myRangePF1 (**datetime2(0)**)  
         AS RANGE RIGHT FOR VALUES ('2022-04-01', '2022-05-01', '2022-06-01') ;  
    GO  
      
    CREATE PARTITION SCHEME myRangePS1  
         AS PARTITION myRangePF1  
         TO (test1fg, test2fg, test3fg, test4fg) ;  
    GO  
      
    CREATE TABLE PartitionTable (col1 **datetime2(0)** PRIMARY KEY, col2 char(10))  
         ON myRangePS1 (col1) ;  
    GO  
    

    Best regards,
    Bert Zhou


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    No comments