Hi, i have column "s" with data type "Date". I want my query to filter that column giving in filter value '2010-10-21' which is todayes day. The filter should get from table "T" all rows where column "s" shows the same day as filter is: '2010-10-21', the minutes and other pieces in column "s" don't matter. So the correct query would both those two below:
WITH t AS
(SELECT SYSDATE - 1 / 24 AS s
FROM Dual
UNION ALL
SELECT SYSDATE - 1 - 1 / 24 FROM Dual)
SELECT * FROM t
WHERE To_Date(t.s, 'YYYY-MM-DD') = To_Date('2010-10-21', 'YYYY-MM-DD') --21.10.2010 16:52:47
WITH t AS
(SELECT SYSDATE - 1 / 24 AS s
FROM Dual
UNION ALL
SELECT SYSDATE - 1 - 1 / 24 FROM Dual)
SELECT * FROM t
WHERE Trunc(t.s) = To_Date('2010-10-21', 'YYYY-MM-DD') --21.10.2010 16:52:47
I want to understand which of those two are better. I understand that "To_Date(t.s, 'YYYY-MM-DD')" converts it's argument firstly to string, and then it converts it successfully to Date no matter what the NLS-parameters are, and then it removes seconds from that Date-object. But trunc seems to do the same but i beleive it maybe removes the seconds more faster way. Should you suggest me to test or can we talk firstly theoretically about the issue? Which is better then of those two?
Edited by: CharlesRoos on 21.10.2010 18:12
I Made a little changein there: WHERE To_Date(t.s, 'YYYY-MM-DD')
Edited by: CharlesRoos on 21.10.2010 18:12
Edited by: CharlesRoos on 21.10.2010 18:15