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

martin.j.evans (AT) gmail (DOT) com wrote:
There are too different issues here. One is storage the other
display. Look at TO_CHAR, TO_DATE, CAST, EXTRACT, TO_DSINTERVAL,
TO_TIMESTAMP, TO_TIMESTAMP_TZ, and other built in functions to
give you the what you need.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org