CODEWITHSUNDEEP
sqloracle-databasedatedistinct
BnJ
BnJ

Reputation: 1044

select distinct(date) return the same date several time

On my Oracle database, I have a table with a column with Date as data type.

When I do this :

select distinct(MY_DATE) from MY_TABLE where extract(year from MY_DATE) = 2014;

I get this :

Result

And when I do this query :

select MY_DATE from MY_TABLE where extract(year from MY_DATE) = 2014;

That's what I have :

Result

I guess it's because of the different seconds and milliseconds.

How to ignore the time in date to have only one result in my fisrt query ?

Upvotes: 2

Views: 16696

Answers (2)

user9846097
user9846097

Reputation: 1

select distinct(date(MY_DATE)) from MY_TABLE where extract(year from MY_DATE) = 2014;

Upvotes: -3

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

Try the Query below, which just adds TRUNC() to your date column before taking a DISTINCT of it.

select distinct(TRUNC(MY_DATE)) from MY_TABLE where extract(year from MY_DATE) = 2014;

This is because the time factor in your column data is not the same.

TRUNC() will nullify the time element, retaining the date only

The query will actually reveal it. TO_CHAR() to output the date saved in the format specified.

select TO_CHAR(MY_DATE,'MM-DD-YYYY HH24:MI:SS') from MY_TABLE where extract(year from MY_DATE) = 2014;

More about TRUNC from Oracle Docs

Upvotes: 10

Related Questions