0

I have a oci8 module installed with PHP and when using a to_date conversion in my query I get an error. When I use the exact same query in my sql developer it works just fine.

php:

include("db_connection.php");


$query = "select count(distinct mp_eic) kogus, asp
from tellitud_eic
where work_type in ('E63001','E90000')
and wo_issued_meter is not null
and to_date(actual_completion, 'dd.mm.yyyy hh24:mi:ss') < '01.01.2014'
group by asp";


$parseQuery = oci_parse($connection, $query);

$result = oci_execute($parseQuery);

$sqlResultJson = array();


while ($row = oci_fetch_array($parseQuery, OCI_ASSOC+OCI_RETURN_NULLS)) {
    array_push($sqlResultJson, $row);
}

$json = json_encode($sqlResultJson);

echo $json;

error: 

Warning:  oci_execute(): ORA-01843: not a valid month in <b>C:\Apache24\htdocs\queries\query_2013_installation_by_asp.php on line 15
Warning:  oci_fetch_array(): ORA-24374: define not done before fetch or execute and fetch in <b>C:\Apache24\htdocs\queries\query_2013_installation_by_asp.phpon line 
[]

I used one other query with a different to_date format and this one worked (to_date(to_char(to_date(p_kuu, 'mm'), 'MONTH'), 'MONTH');)
so I have no idea whats going on.

Maheswaran Ravisankar
  • 17,652
  • 6
  • 47
  • 69
user2970534
  • 41
  • 8
  • use TO_DATE('01.01.2014','DD.MM.YYYY') instead of literal constant. And google NLS_DATE_FORMAT parameter please. – Dmitry Nikiforov Mar 03 '14 at 13:49
  • format mask is correct. I can see the value in actual_completion column like for example '20.08.2013 22:13:56' and it works just fine when I execute it in sqlplus or sql developer. – user2970534 Mar 03 '14 at 14:13
  • ok got it now. I didn't understand your comment previously. now it's working thanks! correct query: select count(distinct mp_eic) kogus, asp from tellitud_eic where work_type in ('E63001','E90000') and wo_issued_meter is not null and to_date(actual_completion, 'dd.mm.yyyy hh24:mi:ss') < to_date('01.01.2014', 'DD.MM.YYYY HH24:MI:SS') group by asp – user2970534 Mar 03 '14 at 14:23

0 Answers0