Oracle DateTime in Where Clause?

Question

I have sql something like this:

SELECT EMP_NAME, DEPT
FROM EMPLOYEE
WHERE TIME_CREATED >= TO_DATE('26/JAN/2011','dd/mon/yyyy')

-> This returns 10 rows and TIME_CREATED = '26-JAN-2011'

Now when i do this i don't get any rows back,

SELECT EMP_NAME, DEPT
    FROM EMPLOYEE
    WHERE TIME_CREATED = TO_DATE('26/JAN/2011','dd/mon/yyyy')

-> Took the greater than out

Any reason why?

You should avoid language dependent date formats. That can cause trouble on different systems. You should use `01` instead of `JAN` (plus the approriate format of course) to make sure your code runs without problem on any system. — , Apr 20 '13 at 16:36

Daniel Hilgarth · Accepted Answer · 2011-07-19T16:16:27.203

175

Yes: TIME_CREATED contains a date and a time. Use TRUNC to strip the time:

SELECT EMP_NAME, DEPT
FROM EMPLOYEE
WHERE TRUNC(TIME_CREATED) = TO_DATE('26/JAN/2011','dd/mon/yyyy')

UPDATE:
As Dave Costa points out in the comment below, this will prevent Oracle from using the index of the column TIME_CREATED if it exists. An alternative approach without this problem is this:

SELECT EMP_NAME, DEPT
FROM EMPLOYEE
WHERE TIME_CREATED >= TO_DATE('26/JAN/2011','dd/mon/yyyy') 
      AND TIME_CREATED < TO_DATE('26/JAN/2011','dd/mon/yyyy') + 1

edited Jul 19 '11 at 16:16

answered Jul 19 '11 at 15:06

Daniel Hilgarth

171,043
40
335
443

15

Note that this approach will prevent the use of an index on TIME_CREATED, if one exists. – Dave Costa Jul 19 '11 at 16:11
Thank you posting the solution. It was quick and easy to find. While I have worked on other DBMS such as Ingres, MS-SQL, MS-Access, and DB2, I have not worked with Oracle before my current assignment. – Jason TEPOORTEN Oct 19 '12 at 03:26
Why not use `BETWEEN TO_DATE('26/JAN/2011','dd/mon/yyyy') AND TO_DATE('26/JAN/2011','dd/mon/yyyy') + 1`? – ajeh Dec 04 '13 at 15:13
2

@ajeh: I don't like `between` because of the ambiguity. It sounds as if it is exclusive the bounds when in fact it is inclusive. That's why I avoid it. Furthermore, in this concrete example, it wouldn't be the same. – Daniel Hilgarth Dec 06 '13 at 18:26

score 39 · Answer 2 · answered Jul 20 '11 at 08:58

39

You can also use the following to include the TIME portion in your query:

SELECT EMP_NAME
     , DEPT
  FROM EMPLOYEE 
 WHERE TIME_CREATED >= TO_DATE('26/JAN/2011 00:00:00', 'dd/mon/yyyy HH24:MI:SS');

answered Jul 20 '11 at 08:58

davidsr

720
4
4

score 17 · Answer 3 · answered May 17 '16 at 15:37

17

You could also do:

SELECT EMP_NAME, DEPT
FROM EMPLOYEE
WHERE TRUNC(TIME_CREATED) = DATE '2011-01-26'

answered May 17 '16 at 15:37

Matas Vaitkevicius

58,075
31
238
265

score 9 · Answer 4 · answered Jul 19 '11 at 15:07

This is because a DATE column in Oracle also contains a time part. The result of the to_date() function is a date with the time set to 00:00:00 and thus it probably doesn't match any rows in the table.

You should use:

SELECT EMP_NAME, DEPT
FROM EMPLOYEE
WHERE trunc(TIME_CREATED) = TO_DATE('26/JAN/2011','dd/mon/yyyy')

score 6 · Answer 5 · answered Jul 19 '11 at 16:16

As other people have commented above, using TRUNC will prevent the use of indexes (if there was an index on TIME_CREATED). To avoid that problem, the query can be structured as

SELECT EMP_NAME, DEPT
FROM EMPLOYEE
WHERE TIME_CREATED BETWEEN TO_DATE('26/JAN/2011','dd/mon/yyyy') 
            AND TO_DATE('26/JAN/2011','dd/mon/yyyy') + INTERVAL '86399' second;

86399 being 1 second less than the number of seconds in a day.

Oracle DateTime in Where Clause?

5 Answers5

Linked