Merge certain DATE partitions before migration to 15.2 ESD #2 [CR #640727]

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:

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

2. 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') );