DATETIME Data Type - Sybase SQL Anywhere to Oracle Migration

Sybase SQL Anywhere DATETIME data type stores date and time data - year, month, hour, minute, second and fraction of a second.

Quick Example:

   -- Define a table with a DATETIME column in Sybase SQL Anywhere
   CREATE TABLE orders
   (
      order_datetime DATETIME 
   );
 
   -- Insert a datetime value - March 20, 2012 05:09:31.123456
   INSERT INTO orders VALUES ('2012-03-20 05:09:31.123456');

Conversion to Oracle

In Oracle, you can use TIMESTAMP or DATE data type. They both can store date and time data.

Conversion to TIMESTAMP ( denotes syntax or functional differences, or more strict restrictions):

Sybase SQL Anywhere Oracle
Syntax DATETIME TIMESTAMP[(p)]
Date Range January 01, 0001 to December 31, 9999 January 1, 4712 BC to December 31, 9999
Time Range 00:00:00.000000 to 23.59.59.999999 00:00:00.000000 to 23.59.59.999999999
Fraction of Second 6 digits (microseconds) 0 ⇐ p ⇐ 9 (nanoseconds), optional, default is 6
Storage Size 8 bytes 7 to 11 bytes

If your database does not use the fraction of seconds in Sybase DATETIME data types, you can convert it to the DATE data type in Oracle:

Sybase SQL Anywhere Oracle
Syntax DATETIME DATE
Date Range January 01, 0001 to December 31, 9999 January 1, 4712 BC to December 31, 9999
Time Range 00:00:00.000000 to 23.59.59.999999 00:00:00 to 23.59.59
Fraction of Second 6 digits (microseconds) no fraction of seconds
Storage Size 8 bytes 7 bytes

Last Update: Sybase SQL Anywhere 12.0 and Oracle 11g

See DATETIME for more details.

Conversion Examples

DATETIME conversion examples.

Sybase SQL Anywhere:

  CREATE TABLE orders
   (
      order_datetime DATETIME 
   );

Oracle:

  CREATE TABLE orders
  (
     order_datetime TIMESTAMP(6) 
  );

Convert Online

Resources