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

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

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

In Oracle you can convert a string into a date using the to_date() function. And a date to a string using the to_char() function

Example

to_date('01-JAN-2015','DD-MON-YYYY')

will return a value of type date.

Now, in your query you must make sure that the values in your "between" statement are date values. The only way to ensure that is to make $sDate a atring value and use the to_date function. Assuming that your date strings are in format 01-JAN-2015...

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

See also the Oracle documentation on date formats. http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm