martin.j.evans_at_gmail.com wrote:
> 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
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_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Jul 25 2006 - 02:51:47 CDT
