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

Question

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).

Answer 1

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

Answer 2

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.