You can do it like this:
#!/usr/bin/perl
use DBI;
use strict;
my ($start,$end) = @ARGV;
my $dbh=DBI->connect("dbi:Oracle:",'system','manager');
my $sql = q[
select '1'
from dual
where to_char(sysdate,'HH24') >= ?
and to_char(sysdate,'HH24') < ?
];
my $count = ($dbh->selectrow_array($sql,undef,$start,$end)) || 0;
print $count,"\n";
----- Original Message -----
From: "Hardy Merrill" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, January 11, 2002 11:01 AM
Subject: DBI and Oracle date comparison
> Here's my query:
>
> SELECT TO_CHAR(SYSDATE, 'HH24') AS TIME
> FROM DUAL
> WHERE TO_CHAR(SYSDATE, 'HH24') >= ?
> AND TO_CHAR(SYSDATE, 'HH24') < ?
>
>
> The TO_CHAR(SYSDATE, 'HH24') produces 11, since the hour
> here now is 11. The 1st placeholder is for $start_time, which
> contains 5, and 2nd placholder is for $stop_time, which contains
> 17.
>
> Since 11 is between 5 and 17, this query should return 1 row,
> but it is not returning any rows. I've tried
>
> my $sth = $dbh->prepare($sql);
> $sth->execute($start_time, $stop_time);
>
> and I've tried
> use DBI qw(:sql_types); ### at the top
> my $sth = $dbh->prepare($sql);
> $sth->bind_param( 1, $start_time, SQL_INTEGER);
> $sth->bind_param( 2, $stop_time, SQL_INTEGER);
> $sth->execute();
>
> but neither way works.
>
>
> ***However, if I don't use placeholders:
>
> SELECT TO_CHAR(SYSDATE, 'HH24') AS TIME
> FROM DUAL
> WHERE TO_CHAR(SYSDATE, 'HH24') >= $start_time
> AND TO_CHAR(SYSDATE, 'HH24') < $stop_time
>
> it works.
>
> I can certainly use this last method, but I want to understand
> why I can't get placeholders to work. Can someone please
> clue me in.
>
> Here is the trace(2) output using SQL_INTEGER bind_params:
>
> DBI::db=HASH(0x847d770) trace level set to 2 in DBI 1.20-nothread
> -> prepare for DBD::Oracle::db (DBI::db=HASH(0x847d7e8)~0x847d770 '
> SELECT TO_CHAR(SYSDATE, 'HH24') AS TIME
> FROM DUAL
> WHERE TO_CHAR(SYSDATE, 'HH24') >= ?
> AND TO_CHAR(SYSDATE, 'HH24') < ?
> ')
> dbd_preparse scanned 2 distinct placeholders
> fbh 1: 'TIME' NULLable, otype 1-> 5, dbsize 2/3, p2.s0
> <- prepare= DBI::st=HASH(0x84c9f88) at New_Pager.pm line 615
> -> execute for DBD::Oracle::st (DBI::st=HASH(0x84c9f88)~0x84dbc34 5
17)
> bind :p1 <== 5 (type 0)
> bind :p1 <== 5 (size 1/2/0, ptype 6, otype 1)
> bind :p2 <== 17 (type 0)
> bind :p2 <== 17 (size 2/3/0, ptype 6, otype 1)
> dbd_st_execute SELECT (out0, lob0)...
> dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0)
> <- execute= '0E0' at New_Pager.pm line 616
> -> trace for DBD::Oracle::db (DBI::db=HASH(0x847d7e8)~0x847d770 0)
> <- trace= 2 at New_Pager.pm line 617
> -> fetchrow_array for DBD::Oracle::st
(DBI::st=HASH(0x84c9f88)~0x84dbc34)
> <- fetchrow_array= ( ) [0 items] at New_Pager.pm line 619
> -> finish for DBD::Oracle::st (DBI::st=HASH(0x84c9f88)~0x84dbc34)
> <- finish= 1 at New_Pager.pm line 623
> -> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x84dbc34)~INNER)
> <- DESTROY= undef at New_Pager.pm line 625
>
> -------------------------------------------------------------------------
>
> TIA.
>
> --
> Hardy Merrill
> Mission Critical Linux, Inc.
> http://www.missioncriticallinux.com
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
