I'm trying to retrieve the date column alone by using the below query but there is no result.

  • What datatype is the REQUESTED_DATE_TIME column? – Andrew Brennan Aug 2 '16 at 14:02

Do not ever rely on implicit type conversion, especially not in cases where it depends on NLS settings.

select * from TABLE NAME where REQUESTED_DATE_TIME = to_date('01-OCT-15', 'DD-MON-YY');

Or even simpler, below syntax uses fixed date format:

select * from TABLE NAME where REQUESTED_DATE_TIME = date'2015-10-01';
  • Both queries are returned only blank result – Iswariya Aug 2 '16 at 14:21
  • 2
    @Iswariya We do not know your data. That may be the correct answer. – Balazs Papp Aug 2 '16 at 14:30
  • Agreed, additionally I think the OP might be trying to SELECT the date column on its own. Something like SELECT REQUESTED_DATE_TIME FROM TABLE_NAME WHERE... Instead of SELECT * – MguerraTorres Aug 2 '16 at 14:36

You will need to use the TO_DATE() function. The first value of the function, you will enter the date in which ever format you want to use, the second value of the function, you are identifying the date format so Oracle knows how to read it.

FROM table_name
WHERE REQUESTED_DATE_TIME = to_date('10/01/2015', 'MM/DD/YYYY');

Also SELECT * will pull all columns from the "table_name" table. If you are trying to only pull the column REQUESTED_DATE_TIME then you will need to use

FROM Table T
WHERE T.REQUESTED_DATE_TIME = to_date('10/01/2015', 'MM/DD/YYYY');

REQUESTED_DATE_TIME seems to include a time portion and there's probably no row for '2015-10-01 00:00:00'.

Either truncate the column to a date:

select * from TABLE NAME 
where TRUNC(REQUESTED_DATE_TIME) = date'2015-10-01';

or compare using >/< (recommended because it might use an index):

select * from TABLE NAME 
where REQUESTED_DATE_TIME >= TIMESTAMP '2015-10-01 00:00:00'
  AND REQUESTED_DATE_TIME <  TIMESTAMP '2015-10-02 00:00:00';

@BalazsPapp already recommended you to use a date/timestamp literal instead of TO_DATE as there's only one valid format: 'yyyy-mm-dd hh:mi:ss'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.