This section is added to Working with Database Objects > Building your Sybase IQ databases > Range partitioning:
In versions before 15.2 ESD #2, Sybase IQ ignored database partitions defined on a column of type DATE when the partition definition contained time portions.
For example, assume that you created this table before migrating to 15.2 ESD #2:
CREATE TABLE pt_date(c1 INT, c2 DATE) IN IQ_SYSTEM_MAIN PARTITION BY RANGE (c2) ( p1 VALUES <= ('2006-03-31' ) , p2 VALUES <= ('2006-06-30:01:02' ) , p3 VALUES <= ('2006-09-30') , p4 VALUES <= ('2006-12-31') )
INSERT INTO pt_date VALUES( 1, '2006-03-30'); INSERT INTO pt_date VALUES( 1, '2006-05-30:01:02'); INSERT INTO pt_date VALUES( 1, '2006-10-30'); COMMIT;
After migration to 15.2 ESD #2, an operation (such as SELECT) on the pt_date table returns the error “cannot convert '2006-06-30:01:02' to a date.”
To avoid such errors:
Before migration, to avoid the error, merge the partition to its next partition. This creates a new partition range and removes the former range:
ALTER TABLE pt_date MERGE PARTITION p2 INTO p3
Split the partition into the desired partition definition with the same partition criteria, but without a time portion:
ALTER TABLE pt_date split PARTITION p3 INTO (P31 VALUES <= ('2006-06-30') , P32 VALUES <= ('2006-09-30') );