getting date from oracle database without time from a column

Question

I want to get details from data base in which date is of format 08-MAR-14 03.52.17.000000000 PM .I know its possible to get date

SELECT * FROM Table WHERE somecol >= '2011-01-01' AND somecol <= '2011-01-30'

in this data i am having time also and how is it possible to get only date and I am using java to connect with oracle and html5+java script+j query.

Use the DATE() function to extract the year-month-day part. E.g. DATE(somecol) > '2011-01-01' — marekful
– marekful, Commented Jun 20, 2014 at 13:34
ResultSet.getDate() will only not include the time portion of the data. docs.oracle.com/javase/7/docs/api/java/sql/… — Brett Okken
– Brett Okken, Commented Jun 20, 2014 at 13:41
It sounds like you are applying conditions on this date as well. You could use trunc(col_date) for your conditions and to_char(col_date, 'YYYY-MM-DD') for how the column values are presented in the select statement. — Patrick Bacon
– Patrick Bacon, Commented Jun 20, 2014 at 14:00
possible duplicate of Oracle: Similar to sysdate but returning only time and only date — Basil Bourque
– Basil Bourque, Commented Jun 20, 2014 at 17:24

swapnil gandhi · Accepted Answer · 2014-06-20 13:51:45Z

1

use to_char function.

Refer this link

answered Jun 20, 2014 at 13:51

swapnil gandhi

7961 gold badge21 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PlanItMichael · Accepted Answer · 2014-06-20 17:46:06Z

0

I'm assuming somecol contains the date field that is actually stored as a VARCHAR2 text string of the format:

08-MAR-14 03.52.17.000000000 PM

If this is the case, here's your query:

select * from table 
  where to_date(substr(somecol, 0, 9)) >= '01-JAN-11'
    and to_date(substr(somecol, 0, 9)) <= '30-JAN-11';

The problem with this is that Oracle needs to execute the substr and to_date on every row in order to know which ones to bring back. If you need to run this frequently on a table with a large number of rows, it would impact your performance. You could create function-based indexes on 'to_date(substr(somecol, 0, 9))' in order to lessen this impact.

answered Jun 20, 2014 at 17:46

PlanItMichael

3341 silver badge9 bronze badges

Collectives™ on Stack Overflow

getting date from oracle database without time from a column

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related