aspose file tools*
The moose likes Oracle/OAS and the fly likes Selecting a DISTINCT date from a Timestamp column Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Selecting a DISTINCT date from a Timestamp column" Watch "Selecting a DISTINCT date from a Timestamp column" New topic
Author

Selecting a DISTINCT date from a Timestamp column

Joe Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9426
    
    2

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!


SCJP 1.4, SCWCD 1.4 - Hints for you, Certified Scrum Master
Did a rm -R / to find out that I lost my entire Linux installation!
Joe Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9426
    
    2

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

Joined: Jan 30, 2009
Posts: 4
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Selecting a DISTINCT date from a Timestamp column