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