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

Overview

Sybase SQL Anywhere DATETIME data type:

Syntax DATETIME
Date Range January 01, 0001 to December 31, 9999
Time Range 00:00:00.000000 to 23.59.59.999999
Fraction of Second 6 digits Microsecond precision
Restrictions Time part cannot be stored before February 02, 1600 and after January 01, 7911
Storage Size 8 bytes
Synonym TIMESTAMP ANSI SQL compliant

Last Update: Sybase SQL Anywhere 12.0

DATETIME Data Type Details

Detailed description of the Sybase SQL Anywhere DATETIME data type.

3 Digits in Fraction of Second

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:

2012-03-20 04:18:31.123
2012-03-20 04:18:31.123

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:

2012-03-20 04:18:31.123456
2012-03-20 04:18:31.123456

Sybase SQL Anywhere DATETIME Conversion to Other Databases

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...

Sybase SQL Anywhere Resources

Sybase SQL Anywhere 12.0 Manual