Sybase SQL Anywhere - DATETIME Data Type - Date and Time Data

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
   CREATE TABLE orders
   (
      order_datetime DATETIME 
   );
 
   -- Insert a datetime value - March 20, 2012 12:24:34.123456
   INSERT INTO orders VALUES ('2012-03-20 12:24:34.123456');

Sybase SQL Anywhere DATETIME data type:

Last Update: Sybase SQL Anywhere 12.0

Detailed description of the Sybase SQL Anywhere DATETIME data type.

The DATETIME data type can store 6 digits for the fraction of a second, but when you query the data, Sybase returns 3 digits by default:

   -- Insert a datetime value with 6 fractional digits
   INSERT INTO orders VALUES ('2012-03-20 04:18:31.123456');
 
   -- Insert a datetime value with 9 fractional digits (no error on insert)
   INSERT INTO orders VALUES ('2012-03-20 04:18:31.123456789');
 
   -- Now query the data
   SELECT * FROM orders;

The result of the query:

The reason is that the Sybase option timestamp_format defines the output format for DATETIME values, and its default value is YYYY-MM-DD HH:NN:SS.SSS that specifies to show 3 digits.

Note that despite timestamp_format value, Sybase still stores 6 fractional digits, so you can output them by changing the format, you do not need to re-insert the data:

  -- Modify output format to show 6 digits
  SET OPTION timestamp_format = 'YYYY-MM-DD HH:NN:SS.SSSSSS'
 
   -- Query the data
   SELECT * FROM orders; 
 
  -- Set the default option back
  SET OPTION timestamp_format = 'YYYY-MM-DD HH:NN:SS.SSS'

Now the result of the query:

Sybase SQL Anywhere DATETIME data type in other databases ( denotes syntax or functional differences, or more strict restrictions):

Oracle:

TIMESTAMP(p)	p optionally specifies fraction of seconds, 0 ⇐ p ⇐ 9	January 1, 4712 BC to December 31, 9999
DATE	no fraction of seconds

More about conversion to Oracle...

• Reference and Articles
• Data Types

Sybase SQL Anywhere 12.0 Manual

• DATETIME Data Type