Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

jdavidb (1361)

jdavidb
  (email not shown publicly)
http://voiceofjohn.blogspot.com/

J. David Blackstone has a Bachelor of Science in Computer Science and Engineering and nine years of experience at a wireless telecommunications company, where he learned Perl and never looked back. J. David has an advantage in that he works really hard, he has a passion for writing good software, and he knows many of the world's best Perl programmers.

Journal of jdavidb (1361)

Monday June 19, 2006
08:18 AM

Oracle gem of the day: gracefully degrading date masks

[ #29951 ]

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.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.