Partition by Range on a Char datatype

[ADBA007]

Hello,
Is it possible to create a table and partition it (by range) on a column that is a “date value” but the datatype is character (character string).
Due to vendor restrictions, we are using character datatype to store date and I am running into ORA-00907 when I try to create a partition table by range on that column.

Thanks.

[slimdave]

Yes you can, but how meaningful it is will depend on how the date is formatted -- if it's formatted YYYY-MM-DD for example you'd be ok, but if it was DD-MON-YYYY then you'd probably be wasting your time.

What a dumb vendor you have.

[ADBA007]

What i am doing wrong with following script:

CREATE TABLE MYTABLE_PARTN ("COL1" CHAR(31), "COL2" CHAR(31), "COL3" CHAR(31))
partition by range (to_date(COL3,'dd-mon-yyyy'))
(
partition p1 values less than (to_date('28-jul-2004','dd-mon-yyyy')) tablespace my_data,
partition p2 values less than (to_date('29-jul-2004','dd-mon-yyyy')) tablespace my_data,
);

[marist89]

You're telling oracle you have a table with a char datatype and you want to range partition on dates. USe something like:

CREATE TABLE MYTABLE_PARTN ("COL1" CHAR(31), "COL2" CHAR(31), "COL3" CHAR(31))
partition by range (COL3)
(
partition p1 values less than ('2004-07-28') tablespace my_data,
partition p2 values less than ('2004-07-29') tablespace my_data,
);

The date format will drive what you use as values for the partitioning clause. This is why slimdave said this design needs some work...