select distinct(date) return the same date several time

Question

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 :

And when I do this query :

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

That's what I have :

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 ?

Solution

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

Comments

[Weishi Z]

column type is DATE, but select TO_CHAR(MY_DATE,'MM-DD-YYYY HH24:MI:SS') indeed gives time with hour, e.g. 01-02-1970 08:00:00. How should I update it to 0 hour, e.g. 01-02-1970 00:00:00 then? Running update ... set ... = '02-JAN-70' won't update the 8:00 row as it is the same date.

[konekoya]

You should provide some explanation. Your answer looks like copy-and-paste from the previous answer.

[DaveyDaveDave]

@konekoya - I don't think it's just copy and pasted, as this answer is doing select distinct(date... as opposed to select distinct(TRUNC..., but yes, some explanation as to why that's better would be a good idea.

[user9846097]

instead of truncate you can convert datetime into date only by using date() function then why you go for truncate..@konekoya