migration from mysql to oracle - problems with time data type

We have an application which works with mysql and has various time datatypes.

The various tables include a mysql data types datetime, date and time. In mysql a date contains only a date, time contains only a time and a datetime contains both a date and a time.

Mostly the datetime fields are used with triggers to set the created or modified datetime of the record. The other instances of time and date only contain a date (no time) and a time (no date). I need to know how I can modify these fields such that the existing code works with a minimum of changes.

Some of the date and time fields in the mysql database are concatenated into a datetime. I can change the existing time fields to timestamp and set my NLS_TIMESTAMP_FORMAT to make timestamps in Oracle work like times in mysql but this then affects the fields which were datetimes in mysql (or have become timestamps in Oracle). Similarly, I can set the NLS_DATE_FORMAT so that Oracle Date fields work like a mysql date field.

What the code currently does is:

insert into table (date) values ('2006-06-01');
insert into table (time) values ('11:10:09');
insert into table (datetime) values ('2006-06-01 11:10:09')

and I cannot come up with any combination that allows all 3.

What is the easiest way of doing this in Oracle? The easiest way would be to create an oracle date field which did not include time, a timestamp field which did not include date and a timestamp field which was both date and time but I cannot work out how to do this. NLS_xxx_FORMAT is too general, a format per field would do the trick.

Thanks.

Martin Received on Mon Jul 24 2006 - 14:47:00 CDT