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

Question

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?

Peter Lang · Accepted Answer · 2010-03-08T08:24:21.263

122

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));

edited Mar 08 '10 at 08:24

answered Mar 08 '10 at 07:09

Peter Lang

54,264
27
148
161

1

@Abdulrhman - you should accept the answer that worked for you! – Leslie Mar 08 '10 at 16:35
trunk() worked like a charm, sweet like chocolate! I should have read your post early. – andre_northwind Jul 12 '15 at 02:28
@SergiyKolesnikov: I don't see how this would happen, please have a look at a row that is returned by one query but not the other and try to investigate what happens. BTW, there is no `<=`, only `<`. – Peter Lang Aug 08 '18 at 12:59
TRUNK + TO_DATE works! Also makes easier because its not so nice write those YYYYMMDDHHMMSS format – davidwillianx Feb 03 '20 at 12:36
For some reason, if you use `>=` or `between`, indexing is not used – ACV Jul 02 '20 at 15:20
1

@ACV: It is used with my tests, but it depends on your table statistics and the percentage of rows that the optimizer expects for your given range. – Peter Lang Jul 03 '20 at 06:09
Thanks @PeterLang. You're right, it's because of I was basically getting back more than 50%-60% of the total rows so the engine decided not to use indexes at all. – ACV Jul 03 '20 at 08:04

score 5 · Answer 2 · answered Oct 19 '12 at 18:58

5

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'

answered Oct 19 '12 at 18:58

JavaSheriff

7,074
20
89
159

Must be carefull with this approach. Insteand choose 'yyyy/mm/MM' when you need date ranges – Yury Euceda Jan 23 '20 at 15:50

score 5 · Answer 3 · answered Mar 08 '10 at 12:25

5

Personally, I usually go with:

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

answered Mar 08 '10 at 12:25

R. Genaro

146
2

Dustin Laine · Answer 4 · 2010-03-08T07:58:09.267

2

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

edited Mar 08 '10 at 07:58

answered Mar 08 '10 at 07:02

Dustin Laine

37,935
10
86
125

3

`BETWEEN` means `>= AND <=`, so your query would return rows with `date = 2010-08-04 00:00:00` too. – Peter Lang Mar 08 '10 at 07:18
Good catch, fixed. Your answer was much more thorough +1. – Dustin Laine Mar 08 '10 at 07:57

score 2 · Answer 5 · edited Jun 21 '17 at 09:01

2

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

edited Jun 21 '17 at 09:01

Bugs

4,491
9
32
41

answered Jun 21 '17 at 09:00

Dave Kuziara

21
3

score 0 · Answer 6 · edited Nov 08 '19 at 04:44

0

Simply use this one:

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

edited Nov 08 '19 at 04:44

NoobTW

2,466
2
24
42

answered Nov 08 '19 at 03:02

Oragon

9
2

Have you tested this query in oracle? – Manjeet Apr 08 '20 at 19:17
This query, in oracle, results in an error: `ORA-01843: not a valid month`. – Paul Stoner Mar 01 '22 at 16:08

score -1 · Answer 7 · answered Mar 08 '10 at 07:26

-1

Try the following way.

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

answered Mar 08 '10 at 07:26

kiruthika

2,155
7
26
33

3

Sorry, but this does not work with Oracle. Gives me an `ORA-00936: missing expression`. – Peter Lang Mar 08 '10 at 07:40
1

In Oracle, double-quotes are used to delimit an identifier; single quotes are used for string literals. – Jeffrey Kemp Mar 08 '10 at 13:41
1

Date() doesn't work in Oracle. For example, `SELECT date(sysdate) FROM dual;` **ERROR at line 1:ORA-00936: missing expression ** – NoelProf Jan 29 '15 at 00:18

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

7 Answers7

Linked