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?
This question is related to
sql
oracle
time
date-arithmetic
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.
You could also do:
SELECT EMP_NAME, DEPT
FROM EMPLOYEE
WHERE TRUNC(TIME_CREATED) = DATE '2011-01-26'
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')
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');
Source: Stackoverflow.com