You don't use the Oracle native datatypes for date/time. That would be the proper solution. Both the insert and select statement should work if you just format the date properly, or tell Oracle what format you are using.

You can set the format of a datetime either in your Oracle client setup, for the session (after you connected to Oracle), or in the SQL statement (with the TO_DATE function).

This is how to set the session format: 

alter session set NLS_TIMESTAMP_FORMAT = 'RRRR-MM-DD HH24:MI:SS';
alter session set NLS_DATE_FORMAT = 'RRRR-MM-DD';
alter session set NLS_TIME_FORMAT = 'HH24:MI:SS';

[download]

You shouldn't trust the client default, it is bound to be wrong somewhere, some time. You could make it easy on yourself and always set a sane default after connecting. Like this: 

my $dbh = ... connect ...;
$dbh->do("alter session set NLS_TIMESTAMP_FORMAT = 'RRRR-MM-DD HH24:MI
+:SS'");
... etc ...

[download]

Google for "oracle nls" for more info about this. NLS stands for National Language Support and is something you should read up on before it bites you again (character sets, non-ASCII chars and such).

/J

In reply to Re: Date/Time insertion problem with Perl/Oracle by jplindstrom
in thread Date/Time insertion problem with Perl/Oracle by NodeReaper

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.