将查询中的oracle日期与PHP进行比较

Question

I want to compare dates retrieved from oracle database in php. but i don't know how to convert month name like Sep to SEP. I don't know how to convert month in capital letters. I want to convert 2015-09-01 formate to 03-SEP-15.

      //sDate: 2015-09-01 eDate: 2015-09-03

$date1=date('d-M-Y', strtotime($sDate));
$date2=date('d-M-Y', strtotime($eDate));

// CREATEDATE: 03-SEP-15 02.44.42.000000 PM

QUERY:

$stid = oci_parse($conn, "SELECT * FROM table1 WHERE CAST(CREATEDATE AS DATE)  between   '".$sDate."' AND  '".$eDate."'");

ERROR:

Warning: oci_execute(): ORA-01861: literal does not match format string

Answer 1

So you have a date string like this: '2015-09-01' and you want to convert it to '01-09-2015' to be used in a query.

If you want to debug if the issue is in how you handle the date conversion in php try to do:

list($year,$month,$day)=explode('-',$date);
$months = ['','JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC'];

$date = implode('-',$day,$months[$month],$year);

Then try to run the query. You don't need to take the string, convert it into a date (unix timestamp) and convert it back to a string if you just need to change the order of the elements in the date. You have to follow your approach if you want to manipulate your date adding or removing some time or stuffs like that.

If the query is still in error and you are sure that dates in your db are stored with that pattern than the issue is in the query syntax