aldrinsanpablo aldrinsanpablo - 29 days ago 13
SQL Question

Oracle SQL: Comparing Extracted Date in Timestamp

I have the following

TABLE.Sample1
:

Col1(varchar) | Col2(timestamp)
---------------------------------------
A | 08-NOV-16 09.59.52.000000000 AM
B | 08-NOV-16 10.05.12.000000000 AM
C | 09-NOV-16 10.05.12.000000000 AM


Suppose I want to get all rows given a date in the format of
DD MON YYYY
.

Here's my initial query:

SELECT *
FROM Sample1
WHERE CAST(Col2 as DATE) = TO_DATE('8 NOV 2016','DD MON YYYY');


I'm curious as to why this query doesn't work on equality(
=
) but works fine on inequalities (
>
,
<
,
<=
,
>=
,
<>
). Am I not comparing the same data types here?

I ended up using
TO_CHAR
function to make this work but it begs the question, is there a simpler or more direct approach to this?

SELECT *
FROM Sample1
WHERE TO_CHAR(CAST(Col2 as DATE)) = TO_CHAR(TO_DATE('8 NOV 2016','DD MON YYYY'));

Answer

In Oracle a DATE value still has a time. CAST(Col2 as DATE) won't change that.

You can use trunc() to set the time part of a date (or timestamp) to 00:00:00 and you shouldn't compare strings, but dates:

SELECT * 
FROM Sample1 
WHERE trunc(col2) = TO_DATE('8 NOV 2016','DD MON YYYY');

But I strongly recommend to not use a date format like that. It depends on the NLS settings of the SQL client program and might fail with different language settings.

I prefer using ANSI SQL date literals:

SELECT * 
FROM Sample1 
WHERE trunc(col2) = DATE '2016-11-08';

In both cases, the date will have a time of 00:00:00 because none was given and thus the comparison will work.

Note that trunc(col2) will not be able to use an index on col2. If performance is important you should either use a range query where col2 >= DATE '2016-11-08' and col2 < DATE '2016-11-09' or create a function based index on trunc(col2).