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');

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.

DATETIME conversion examples.

Sybase SQL Anywhere:

  CREATE TABLE orders
   (
      order_datetime DATETIME 
   );

Oracle:

  CREATE TABLE orders
  (
     order_datetime TIMESTAMP(6) 
  );

Sybase SQL Anywhere

• Reference and Articles
• Data Types
• DATETIME Data Type

Oracle

• Reference and Articles