Perl Guru Forums: Perl Programming Help: DBI: Passing dates as variables

CGI/Perl Guide | Learning Center | Forums | Advertise | Login

Site Search: in


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	LOG IN

Home: Perl Programming Help: DBI:
Passing dates as variables

streamlineIC
New User

Apr 14, 2010, 1:27 PM

Post #1 of 3 (8068 views)

Passing dates as variables	Can't Post

I am unable to pass dates to my DBI connection in a form other than dd-mmm-yyyy. Any other attempt throws a "DBD ERROR: error possibly near <*>..." The above format returns no records though. I am using Date::Manip to create and loop through dates, adding 7 days for each pass. Here is what my statement looks like:

my $stmtRecs = "SELECT * FROM test_table WHERE create_date_time BETWEEN \'${startdate}'\ AND \'${enddate}'\;
my $sth = $dbh->prepare($stmtRecs);
$sth->execute() or die $dbh->errstr;

I also tried hard-coding the dates and still get the same error:
my $stmtRecs = "SELECT * FROM test_table WHERE create_date_time BETWEEN '05-04-2010' AND '11-04-2010'";

Any help on what I am doing wrong would be greatly appreciated. If any other information is needed, please let me know and I will post it.

roolic
User

Apr 14, 2010, 8:54 PM

Post #2 of 3 (8050 views)

Re: [streamlineIC] Passing dates as variables [In reply to]	Can't Post

if we are talking about the oracle the date field types should checked via oracle to_date() function call

see here http://www.techonthenet.com/oracle/functions/to_date.php

Code

my ($startdate, $enddate) = ('05-04-2010', '11-04-2010') ; 
my $stmtRecs = "SELECT * FROM test_table WHERE create_date_time BETWEEN to_date(?,'MM-DD-YYYY') AND to_date(?,'MM-DD-YYYY')";  
my $sth = $dbh->prepare($stmtRecs);  
$sth->execute($startdate, $enddate) or die $dbh->errstr;

generally it's more comfortable for perl to use *nix timestamp (db field type is unsigned integer not "date") instead of db's date which is hard to convert.

(This post was edited by roolic on Apr 14, 2010, 9:06 PM)

streamlineIC
New User

Apr 15, 2010, 5:26 AM

Post #3 of 3 (8027 views)

Re: [roolic] Passing dates as variables [In reply to]	Can't Post

In Reply To

if we are talking about the oracle the date field types should checked via oracle to_date() function call

see here http://www.techonthenet.com/oracle/functions/to_date.php

Code

 my ($startdate, $enddate) = ('05-04-2010', '11-04-2010') ;  
my $stmtRecs = "SELECT * FROM test_table WHERE create_date_time BETWEEN to_date(?,'MM-DD-YYYY') AND to_date(?,'MM-DD-YYYY')";   
my $sth = $dbh->prepare($stmtRecs);   
$sth->execute($startdate, $enddate) or die $dbh->errstr;

generally it's more comfortable for perl to use *nix timestamp (db field type is unsigned integer not "date") instead of db's date which is hard to convert.

The formatting of the date was my problem. Once I got the To_Date function in the correct format, I was able to return the desired results. Thanks for the assistance.

Search for (options)

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives