Vishvambruth JT Vishvambruth JT - 2 months ago 10
SQL Question

How to search for the records between specific CREATE_DATE range

I have time stamp in the CREATE_DATE column of my table in the below format.

9/8/2016 5:37:35 AM


I need to search the records between specific CREATE_DATE range. Please help me on how to do the same.

I have used the below query but getting
ORA-01843: not a valid month

select *
from gdf.msg_pyld
where dbms_lob.instr(pyld_clob,'4861615654')>=1
and CREATE_DATE BETWEEN '09/08/2016 5:59:17 AM' AND '09/08/2016 5:59:17 PM';

Answer

What you tried is a comparison of a date (a numeric type with a very special meaning) against STRINGS. This will never work.

If CREATE_DATE is indeed a DATE in Oracle, then this should work:

... AND CREATE_DATE BETWEEN to_date('09/08/2016 5:59:17 AM', 'mm/dd/yyyy hh:mi:ss AM') 
                        AND to_date('09/08/2016 5:59:17 PM', 'mm/dd/yyyy hh:mi:ss PM') 

assuming 09/08 means September 8; if instead it is supposed to mean 9 August, then change mm/dd to dd/mm in the format models. Good luck!