SELECT DISTINCT Returning duplicate values for DATE column

Question

I'm trying to find the range of date values for a date datatype column in my one of my views.

I've searched many oracle forums looking for similar bug and no luck.

Columns for EPAServiceReport view:

EPASERVICEREPORT_KEY    NUMBER(10)
EQUIPMENT_KEY           NUMBER(10)
STARIDNO                VARCHAR2(60)
WORKORDERNUMBER         NVARCHAR2(50)
REPORT_ID               NVARCHAR2(50)
CREATEDDATE             DATE

SQL:

SELECT DISTINCT createddate 
FROM epaservicereport 
ORDER BY createddate

Results:

12-OCT-15
12-OCT-15
19-OCT-15
19-OCT-15
27-OCT-15
30-OCT-15
04-NOV-15
05-NOV-15
12-NOV-15
12-NOV-15

I expected to only see 1 row per value i.e. "distinct" but instead got every row from the view returned with many duplicate values.

user_3844458 · Answer

Your date column has time, too. So although it displays the same but actually it's different in time on a day.

You could use this to get distint date only:

SELECT DISTINCT TRUNC(createddate)
FROM epaservicereport 
ORDER BY 1; --TRUNC(createddate)

user_2549110 · Answer

You can also use this:

SQL Server:

select distinct CONVERT(varchar(12),createddate,105)
FROM epaservicereport 
ORDER BY 1;

SELECT DISTINCT Returning duplicate values for DATE column

2 Answers