File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/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 EJB 3 in Action this week in the EJB and other Java EE Technologies 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: 9243
    
    1

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, SCBCD Hints - Demnachst, SCDJWS - Auch Demnachst
Did a rm -R / to find out that I lost my entire Linux installation!
Joe Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9243
    
    1

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.
 
wood burning stoves
 
subject: Selecting a DISTINCT date from a Timestamp column
 
Similar Threads
UNION for Columns of CLOB data type does not work
Need help with a query (HQL)
Counting identical items in DB
Invalid composite primary key specification
SQL return substrings