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

.

Relevant Pages

  • Re: [PHP] checking user input of MM-DD-YYYY
    ... Because I'm inserting it into MySQL as a date conversion from American ... All the more reason I would turn it into a timestamp or DateTime ... If you enter it in that format MySQL will get it right without regard ...
    (php.general)
  • Re: Want search on timestamp ! Any other alternative ??
    ... > It is BETTER to store timestamps as INTs rather than DATETIME. ... forget the exact date) which MySQL can handle but a Unix timestamp can't? ... Kailash Nadh | http://kailashnadh.name ...
    (comp.lang.php)
  • Re: migration from mysql to oracle - problems with time data type
    ... The various tables include a mysql data types datetime, ... I can change the existing time fields to timestamp and ... mysql (or have become timestamps in Oracle). ...
    (comp.databases.oracle.misc)
  • Re: MySqls equivalence to MsSqls TOP command/function/clause
    ... You want the The LIMIT clause which is used to limit the records returned by ... Have a look a the docs for mySQL ... > command/function/clause and I'm looking for some equivalence in MySql ... > A DateTime column with some swedish 100% unique formatted dateTime rows, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Building a news management system (full story)
    ... I'm building a news site, to wich a user can add new items into a mySQL ... creation (datetime) // datetime when item was created ... is it even a MySQL problem? ...
    (comp.lang.php)