On Tue, 11 Feb 2003, JT MacNeil wrote:
> Hello,
> I am having a small problem with inserting a date into a table. I'm
> working on an accounting app, and I have to insert the due date into a
> table, which is 30 days in the future.
>
> I try the following:
> my $sthCreateOEHeader =
> $dbConn->prepare('INSERT INTO thistab (date) VALUES (?);
> ...snip...
> $sthCreateOEHeader->execute('now()');
>
> This works, great. Now if I do this:
> $sthCreateOEHeader->execute('now() + 30');
>
> it carps out with:
> DBD::Pg::st execute failed: ERROR: Bad date external representation
> 'now() + 30'...
>
That is because when you call execute('now() +30) DBD::Pg will quote
'now() +30', so the resultant SQL statement will look something like this:
INSERT INTO thistab (date) VALUES ('now()+30)')
What what you want is:
INSERT INTO thistab (date) VALUES (now() +30)
> Any idea how I could do this. I'd rather not have to create the date in
> my program, since Postgres can do this for me. BTW, when I do this under
> psql, there is no problem.
>
Here are a few options:
1. You can hack it by binding the column as an integer (which will work
for now but is non-portable and will probably break in a future version).
2. You can create a statement that has the now()+30 already in it.
3. You might be able to get away with doing a $prepare->(q{INSERT INTO
thistab VALUES(now() + ?});
4. Create the sql dynamically.
-r
