CODEWITHSUNDEEP
sqloracle-database
Jfontenot
Jfontenot

Reputation: 41

SELECT DISTINCT Returning duplicate values for DATE column

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.

Upvotes: 4

Views: 1295

Answers (2)

Gauravsa
Gauravsa

Reputation: 6528

You can also use this:

SQL Server:

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

More on cast and convert here:

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

Oracle:

select distinct TO_CHAR(createddate,'YYYY-MM-DD')
FROM epaservicereport 
ORDER BY 1;

Upvotes: 0

Pham X. Bach
Pham X. Bach

Reputation: 5442

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)

Upvotes: 7

Related Questions