I've got data that contains datetime values of differing resolutions: sometimes fields will contain fractional seconds; sometimes it will not. I've just learned that Oracle's TO_TIMESTAMP function gracefully degrades in the presence of a value of less precision than the date format string I specify. For example, given a table TSTAMP that contains a TIMESTAMP value (or DATE value) like this:
CREATE TABLE tstamp (tstamp TIMESTAMP);
the following inserted values all work correctly:
INSERT INTO tstamp
VALUES
(TO_TIMESTAMP('2004-12-31T15:13:02.12345', 'YYYY-MM-DD"T"HH24:MI:SS.FF'));
INSERT INTO tstamp
VALUES
(TO_TIMESTAMP('2004-12-31T15:13:02', 'YYYY-MM-DD"T"HH24:MI:SS.FF'));
INSERT INTO tstamp
VALUES
(TO_TIMESTAMP('2004-12-31', 'YYYY-MM-DD"T"HH24:MI:SS.FF'));
This is handy because I don't want to bother including logic at the Perl level to think about what conversion function to include in my INSERT statement.