CODEWITHSUNDEEP
sqloracle-databasedate
Abdulrhman
Abdulrhman

Reputation: 597

Select from table by knowing only date without time (ORACLE)

I'm trying to retrieve records from table by knowing the date in column contains date and time.

Suppose I have table called t1 which contains only two column name and date respectively.

The data stored in column date like this 8/3/2010 12:34:20 PM.

I want to retrieve this record by this query for example (note I don't put the time):

Select * From t1 Where date="8/3/2010"

This query give me nothing !

How can I retrieve date by knowing only date without the time?

Upvotes: 58

Views: 220547

Answers (7)

Oragon
Oragon

Reputation: 7

Simply use this one:

select * from t1 where to_date(date_column)='8/3/2010'

Upvotes: 0

Dave Kuziara
Dave Kuziara

Reputation: 31

trunc(my_date,'DD') will give you just the date and not the time in Oracle.

Upvotes: 3

JavaSheriff
JavaSheriff

Reputation: 7665

Convert your date column to the correct format and compare:

SELECT * From my_table WHERE to_char(my_table.my_date_col,'MM/dd/yyyy') = '8/3/2010'

This part

to_char(my_table.my_date_col,'MM/dd/yyyy')

Will result in string '8/3/2010'

Upvotes: 5

R. Genaro
R. Genaro

Reputation: 146

Personally, I usually go with:

select * 
from   t1
where  date between trunc( :somedate )          -- 00:00:00
            and     trunc( :somedate ) + .99999 -- 23:59:59

Upvotes: 5

Peter Lang
Peter Lang

Reputation: 55524

DATE is a reserved keyword in Oracle, so I'm using column-name your_date instead.

If you have an index on your_date, I would use

WHERE your_date >= TO_DATE('2010-08-03', 'YYYY-MM-DD')
  AND your_date <  TO_DATE('2010-08-04', 'YYYY-MM-DD')

or BETWEEN:

WHERE your_date BETWEEN TO_DATE('2010-08-03', 'YYYY-MM-DD')
                    AND TO_DATE('2010-08-03 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

If there is no index or if there are not too many records

WHERE TRUNC(your_date) = TO_DATE('2010-08-03', 'YYYY-MM-DD')

should be sufficient. TRUNC without parameter removes hours, minutes and seconds from a DATE.

If performance really matters, consider putting a Function Based Index on that column:

CREATE INDEX trunc_date_idx ON t1(TRUNC(your_date));

Upvotes: 124

Dustin Laine
Dustin Laine

Reputation: 38503

You could use the between function to get all records between 2010-08-03 00:00:00:000 AND 2010-08-03 23:59:59:000

Upvotes: 2

kiruthika
kiruthika

Reputation: 2189

Try the following way.

Select * from t1 where date(col_name)="8/3/2010"

Upvotes: -1

Related Questions