phporacle-databasedatecodeigniterbetween

SELECT Oracle database table records WHERE the date column is BETWEEN two dates coming from PHP variables

retrieving items from table that fall between a date range.

the date (db table field name is called: submission_date) is being stored in the database as d-M-y (ex: 21-Dec-10)

This is being stored in an oracle database, as sysdate. (it needs to stay in the database as that format, so changing the format of how it is stored is not an option)

I want to convert 21-Dec-10 to 20101221, so I can compare it to date the user has posted, which are two values, end_date, begin_date

All I need is to properly convert submission_date to Ymd (20101221)

below is in theory what I want to do:

select 
 * 
from 
 table 
where 
 (convert(Ymd=>submission_date) >= $begin_date 
AND 
 convert(Ymd=>submission_date) <= $end_date)
Solution

  • If the column submission_date is of DATE datatype and $begin_date and $end_date are strings of yyyymmdd format then you could use the following query to retrieve rows that fall between a date range:

    SELECT *
  FROM tab
 WHERE submission_date BETWEEN 
        TO_DATE ( $begin_date, 'yyyymmdd') AND 
        TO_DATE ( $end_date, 'yyyymmdd');

    By not applying a function on submission_date, we give Oracle a chance to use an index on that column, if an index exists.