...
WHERE date_column = TO_DATE(?, 'YYYY-MM-DD')
...

my $date_str = '2015-08-08';
$sth->execute($date_str);

# OR
my $date_fmt = my $datetime_fmt = my $datetime_tz_fmt = 'YYYY-MM-DD HH
+24:MI:SS';
$dbh->do("alter session set nls_date_format=$date_fmt");
$dbh->do("alter session set nls_timestamp_format=$datetime_fmt");
$dbh->do("alter session set nls_timestamp_tz_format=$datetime_tz_fmt")
+;

...
WHERE date_column = ?
...
my $date_str = '2015-08-08';
$sth->execute($date_str);
[download]

$start_date = '2015-08-01';
$end_date = '2015-09-01';
...
WHERE date_column >= ? and date_column < ?
...
$sth->execute($start_date, $end_date);
[download]

If the previous suggestion does not work, in order to make sure the module is interpreting your data as you intend, you might want to do something like:

use DBI qw(:sql_types);
...
$sth->bind_param( 1, $date, SQL_VARCHAR );
$sth->execute();
...
[download]

I was just reading up on bind_param and the different placeholders DBD::Oracle supports. I'll try this out and see if it works. :)

rows

$rv = $sth->rows;

Returns the number of rows affected for updates, deletes and inserts and -1 for selects.

$sth->execute($date) or die "Couldn't get values from table: " . DBI->
+errstr;

my @data;
while (my @row = $sth->fetchrow_array) {
    @data = @row;
}
if (@data) {
    local $" = ',';
    print "@data\n";
} else {
    "0 results\n";
    exit;
}
[download]

I used "date" to keep things simple here. :) The actual statement looks like this:

my $sth_1 = $dbh->prepare("SELECT sum(EMAIL_IRT),sum(DROP_CALL_COUNT),
+sum(ANS_CALL_COUNT),sum(EMAILS_IN),sum(EMAILS_OUT) FROM CS_STATS WHER
+E DATE_ADDED LIKE ?");
[download]

Also, I removed the row->0 if statement completely but still have the same problem - it looks like nothing is returned. This is what I have now:

my $sth_1 = $dbh->prepare("SELECT sum(EMAIL_IRT),sum(DROP_CALL_COUNT),
+sum(ANS_CALL_COUNT),sum(EMAILS_IN),sum(EMAILS_OUT) FROM CS_STATS WHER
+E DATE_ADDED LIKE ?");

$sth_1->execute($date_1) or die "Couldn't get values from DB: " . DBI-
+>errstr;

$sth_1->execute();

my ($email_irt,$drop_call_count,$ans_call_count,$emails_in,$emails_out
+);

while (my @data = $sth_1->fetchrow_array()) {
    $email_irt = $data[0];
    $drop_call_count = $data[1];
    $ans_call_count = $data[2];
    $emails_in = $data[3];
    $emails_out = $data[4];
    }

print "$email_irt,$drop_call_count,$ans_call_count,$emails_in,
[download]

And this is the output:

Use of uninitialized value $email_irt in concatenation (.) or string a
+t monthly_stat_mailer.pl line 88.
Use of uninitialized value $drop_call_count in concatenation (.) or st
+ring at monthly_stat_mailer.pl line 88.
Use of uninitialized value $ans_call_count in concatenation (.) or str
+ing at monthly_stat_mailer.pl line 88.
Use of uninitialized value $emails_in in concatenation (.) or string a
+t monthly_stat_mailer.pl line 88.
Use of uninitialized value $emails_out in concatenation (.) or string 
+at monthly_stat_mailer.pl line 88.
,,,,
[download]

$sth_1->execute($date_1) or die "Couldn't get values from DB: " . DBI-
+>errstr;

$sth_1->execute();
[download]

Do you really run execute twice?

لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ

You did not run the code I posted, and you never actually test if any assignment happens before outputting. That's why I had an array scoped outside the while loop, and then tested it before getting into output. When I asked you to run specific code, it was to check what's coming back from the DB call.

$sth_1->execute($date_1) or die "Couldn't get values from DB: " . DBI-
+>errstr;
$sth_1->execute();
[download]

Is this your actual code? The second execute is clearing the cursor.

Try updating your connector to

my $dbh = DBI->connect( 'DBI:Oracle:SID',
                        'username',
                        'password',
                        {
                            PrintError  => 0,
                            RaiseError  => 1,
                            AutoCommit  => 0,
                        },
                    );
[download]

This way, if this is a database error, the statement handles will all autodie on errors and print out exactly what went wrong. It'll also tell you if you've reset a cursor with a second execute.

---

#11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.

You're only posting part of your code to keep this simple, but it's getting in the way of us helping. See http://sscce.org.

Dum Spiro Spero

my $SQL = 'SELECT ID, DDATE FROM MYTEMP4
           WHERE EXTRACT(MONTH FROM DDATE) = ?
             AND EXTRACT(YEAR FROM DDATE) = ?';
my $sth = $dbh->prepare($SQL);
$sth->execute(8,2015);
while (my @f = $sth->fetchrow_array){
  print join " ",@f,"\n";
}
[download]

Update : If you really want to use LIKE then

my $SQL = "SELECT ID, DDATE FROM MYTEMP4
           WHERE to_char(DDATE,'mm/dd/yyyy') LIKE ?";
my $sth = $dbh->prepare($SQL);
$sth->execute('08/%/2015');
[download]

You're trying to perform a string comparison of the string '08/%/2015' with a date field, which is a number. This absolutely will not work in Microsoft SQL and I suspect (though can't test) that it won't work in Oracle either.

A better solution is: SELECT ... WHERE DATE >= $startdate AND DATE <= $enddate

UPDATE: Yeah, dunno who down-voted me, but there are some assumptions I made:

Although a "WHERE date_column LIKE '...'" clause CAN work in Oracle, it is incredibly poor practice. You can not depend on the date format in any particular environment, and any indexes on the date column will not be used.

Aah, that does work in Oracle? I stand corrected :)

Thanks. I just tried but still have the same problem. :( Also, since I'm using single quotes do I need to escape the % sign?

I am working in mysql. If i want previous month data i write code like this. Dont know whether works in sql

$month=`date --date='1 month ago' +%B`;
.
.
.
my $sth = $dbh->prepare("SELECT * from $table where monthname(Date)='$
+month'" );
.
.
.
[download]