Please disregard. The code is fine. It was a freakin data problem with my test DB.
-----Original Message----- From: Matthew Dougerty [mailto:[EMAIL PROTECTED] Sent: Sunday, October 16, 2005 3:25 PM To: [email protected] Subject: Oracle DBD TO_DATE not working. I am having trouble using date operations in Oracle via DBI. The following code fails to return any results. $selectStatement.="SELECT s.NETWORK_NAME as NETWORK_NAME,"; $selectStatement.=" TO_CHAR(s.OUTAGE_START,'MM/DD/YYYY HH24:MI:SS') as OUTAGE_START,"; $selectStatement.=" TO_CHAR(s.IMPLEMENTATION_DATE, 'MM/DD/YYYY HH24:MI:SS') as IMPLEMENTATION_DATE"; $selectStatement.=" FROM SC.PFZOUTAGEM1 s"; $selectStatement.=" WHERE"; $selectStatement.=" (s.OUTAGE_START < TO_DATE(?,'dd-mm-yyyy hh24:mi:ss')"; $selectStatement.=" and s.OUTAGE_START >= TO_DATE(?,'dd-mm-yyyy hh24:mi:ss')"; $selectStatement.=")"; DBI->trace(2); my $Qselect=$dbh->prepare($selectStatement); $GMTIMEFUTURE='16-10-2005 16:48:00'; $GMTIME='16-10-2005 16:43:00'; $Qselect->execute($GMTIMEFUTURE, $GMTIME); The following query in Toad Returns one row (I set the values so the query would work). SELECT s.NETWORK_NAME as NETWORK_NAME, TO_CHAR(s.OUTAGE_START,'MM/DD/YYYY HH24:MI:SS') as OUTAGE_START, TO_CHAR(s.IMPLEMENTATION_DATE, 'MM/DD/YYYY HH24:MI:SS') as IMPLEMENTATION_DATE FROM SC.PFZOUTAGEM1 s WHERE (s.OUTAGE_START < TO_DATE('16-10-2005 16:48:00','dd-mm-yyyy hh24:mi:ss') and s.OUTAGE_START = TO_DATE('16-10-2005 16:43:00','dd-mm-yyyy hh24:mi:ss')) Here is the trace output from DBI: DBI 1.48-nothread default trace level set to 0x0/2 (pid 21322) -> prepare for DBD::Oracle::db (DBI::db=HASH(0x5e2ce8)~0x626274 'SELECT s.NETWORK_NAME as NETWORK_NAME, TO_CHAR(s.OUTAGE_START, 'MM/DD/YYYY HH24:MI:SS') as OUTAGE_START, TO_CHAR (s.IMPLEMENTATION_DATE, 'MM/DD/YYYY HH24:MI:SS') as IMPLEMENTATION_DATE FROM SC.PF ZOUTAGEM1 s WHERE (s.OUTAGE_START < TO_DATE(?,'dd-mm-yyyy hh24:mi:ss') and s.OUTAGE_START = TO_DATE(?,'dd-mm-yyyy hh24:mi:ss'))') dbd_preparse scanned 2 distinct placeholders fbh 1: 'NETWORK_NAME' NULLable, otype 1-> 5, dbsize 90/91, p90.s0 fbh 2: 'OUTAGE_START' NULLable, otype 1-> 5, dbsize 19/20, p19.s0 fbh 3: 'IMPLEMENTATION_DATE' NULLable, otype 1-> 5, dbsize 19/20, p19.s0 <- prepare= DBI::st=HASH(0x626d84) at InMaintenance.pl line 101 -> execute for DBD::Oracle::st (DBI::st=HASH(0x626d84)~0x626e98 '16-10- 2005 16:48:00' '16-10-2005 16:43:00') bind :p1 <== '16-10-2005 16:48:00' (type 0) bind :p1 <== '16-10-2005 16:48:00' (size 19/20/0, ptype 4, otype 1) bind :p2 <== '16-10-2005 16:43:00' (type 0) bind :p2 <== '16-10-2005 16:43:00' (size 19/20/0, ptype 4, otype 1) dbd_st_execute SELECT (out0, lob0)... dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0) <- execute= '0E0' at InMaintenance.pl line 111 -> $DBI::err (*) FETCH from lasth=HASH <- $DBI::err= undef -> fetchrow_hashref for DBD::Oracle::st (DBI::st=HASH(0x626d84)~0x626e98) 1 -> FETCH for DBD::Oracle::st (DBI::st=HASH(0x626e98)~INNER 'NAME') 1 <- FETCH= [ 'NETWORK_NAME' 'OUTAGE_START' 'IMPLEMENTATION_DATE' ] at InMaintenance.pl line 131 1 -> fetch for DBD::Oracle::st (DBI::st=HASH(0x626e98)~INNER) 1 <- fetch= undef at InMaintenance.pl line 131 <- fetchrow_hashref= undef at InMaintenance.pl line 131 -> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x626e98)~INNER) <- DESTROY= undef -- DBI::END -> disconnect_all for DBD::Oracle::dr (DBI::dr=HASH(0x242b90)~0x5e2c88) <- disconnect_all= (not implemented) at DBI.pm line 677 ! -> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x626274)~INNER) ! <- DESTROY= undef during global destruction ! -> DESTROY in DBD::_::common for DBD::Oracle::dr (DBI::dr=HASH(0x5e2c88) ~INNER) ! <- DESTROY= undef during global destruction Any advice would be helpful. Matt Dougherty [EMAIL PROTECTED]
