Selecting a DISTINCT date from a Timestamp column

[Joe San]

Guys,

Is there any Oracle function using which I can select a DISTINCE date from a Timestamp column? I have a column in one of my table that is a Timestamp and I want to select DISTINCT dates from it, but it is returning me also the results with the same date just because of the different associated time on the same date. Please help!

[Joe San]

Ok. Got it, I used a trunc function to get this done.

[sowmy raj]

What do you mean by "date" in this question?

Are you talking about an Oracle DATE data type-- a structure that has a day and a time component with a precision of a second? Or are you talking about a date in terms of the combination of a day, month, and year with no time component?

You can apply a TRUNC to the timestamp to truncate the timestamp to whatever precision you would like, i.e.


SELECT DISTINCT TRUNC( <<timestamp>>, 'HH24' )
FROM dual;

SELECT DISTINCT TRUNC( <<timestamp>>, 'SS' )
FROM dual

SELECT DISTINCT TRUNC( <<timestamp>>, 'DD' )
FROM dual



give you the distinct Oracle dates with a granularity of hours, seconds, and days respectively.