Oracle select using date and time

Oracle date handling has a few implicit conversion gotchas that will quietly give you wrong results if you’re not careful. Always use TO_DATE with an explicit format string rather than relying on implicit conversion — the default format is session-dependent and will behave differently across environments.

• Create table

create table MESSAGE_STORE(
    message varchar2(200)
    whenreceived date
);

• Oracle select record within a range using operator and date time

SELECT WHENRECEIVED,MESSAGE FROM MESSAGE_STORE
WHERE
WHENRECEIVED > TO_DATE('2012-01-15 18:04:21', 'YYYY-MM-DD HH24:MI:SS')
AND WHENRECEIVED < TO_DATE('2012-01-16 18:10:21', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY WHENRECEIVED desc;

• Oracle select record within a range using between and date time

SELECT WHENRECEIVED,MESSAGE FROM MESSAGE_STORE
WHERE
WHENRECEIVED between TO_DATE('2012-01-15 18:04:21', 'YYYY-MM-DD HH24:MI:SS')
AND TO_DATE('2012-01-16 18:10:21', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY WHENRECEIVED desc;