Robert Hicks wrote:
> Hardy Merrill wrote:
>> Sorry for the top-post - Groupwise :-(
>>
>> Notice how Philip suggested using "to_char" - *not* "to_date".
>>
>> You probably already know this, but on the chance you don't,
>> you use "to_date" if you have a string that contains a date and
>> you want to put that date into a "DATE" column in the database.
>> You use "to_char" if you want to pull a "DATE" column out of
>> the database into a string (scalar) variable.
>>
> I think I get it yes. So here is what I am doing. Access has a date
> field that I am pulling out and when I print the "$start_date"
> variable it looks like this:
>
> 2006-09-15 00:00:00
>
> That is a string now to Perl...correct?
Yes.
> Now I am inserted that string into the Oracle database as a DATE. So I
> am doing, using the variable from the bindcolumn parameter:
>
> TO_DATE($start_date, 'MM/DD/YYYY')
>
> to insert that string into Oracle as a DATE and passing in the date
> format along with it.
>
> Do I have that right?
Yeah, pretty much. You should use bind parameters to pass the date into
Oracle, though -- NOT put it directly in the string. This ensures you
will have no problems with quoting or nulls.
Example:
my $sql = qq{
INSERT INTO MY_TABLE (THE_DATE)
VALUES (TO_DATE(?,'YYYY-MM-DD HH24:MI:SS'))
};
my $sth = $dbh->prepare($sql) || die $dbh->errstr;
while (my $date = get_date_from_access()) {
$sth->execute($date) || die $sth->errstr;
}
Philip
