Select distinct days from a date column in oracle database

Select distinct days from a date column in oracle database

Say now we have a requirement that there is a "created" column of DATE type in the "blogs" table that records the time stamp a blog is posted, and we need to display a list of days that have blog posts. In Oracle, we can use DATE functions to achieve this.

Here we need to ignore the time part of a date and use the date with the granularity of a day. The relevant DATE function is TRUNC. Our list of days can be retrieved by

SELECT DISTINCT TRUNC(created, 'DDD')) FROM blogs;

When you use the TRUNC function, pay attention to the difference between 'DAY' and 'DDD'. The former is the starting day of the week, and the latter is the usual day. Try

SELECT TO_CHAR(TRUNC(TO_DATE('2011-06-06 21:51:55'
  , 'YYYY-MM-DD HH24:MI:SS'), 'DAY'), 'YYYY-MM-DD HH24:MI:SS')
FROM DUAL;  /* 2011-06-05 00:00:00 */

and

SELECT TO_CHAR(TRUNC(TO_DATE('2011-06-06 21:51:55'
  , 'YYYY-MM-DD HH24:MI:SS'), 'DDD'), 'YYYY-MM-DD HH24:MI:SS')
FROM DUAL;  /* 2011-06-06 00:00:00 */

Of course, this case can be generalized to using different granularity, like showing a list of months that have blog posts. More information about formatting options which TRUNC supports can be found in the Oracle documentation.

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.

More information about formatting options

To prevent automated spam submissions leave this field empty.