Oracle date to hundredths of a second
Oracle Tips by Burleson Consulting
Question: In have
incoming data that records hundredths of a second and I cannot find
a DATE format (i.e. nls_date_format) to hold hundredths of a second.
Answer: It's tempting to try the
SSSS date format, but you annot use the "SSSS" format for milliseconds because
SSSS is the number of seconds after midnight.
Instead, you need to use the TIMESTAMP datatype instead of the DATE datatype.
The TIMESTAMP datatype has fractional seconds, as noted by the FF notation:
For example, you can define a TIMESTAMP column
and store data into it, something like this:
create table t1 (timecol TIMESTAMP);
insert into T1 (timecol) values
this query will convert to fractional seconds:
to_char(sysdate, 'DD-Mon-YYYY HH24:MI:SS'),
)||'.55', 'DD-Mon-YYYY HH24:MI:SS.FF')
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.