I have a simple database designed mostly for educational purposes. So, here is this table named "RENTAL".

It has a few columns, but the one that interests me the most is the first one. It is named "BOOK_DATE", telling us the date when a given film is booked. I do not understand why this does this query:

select book_date, count(*)
from rental
group by book_date;

return this:

Why aren't the two 24-SEP-21 grouped together? (The same for 26-SEP-21). Can they be introducted in different formats, or is some logical error I make?

CodePudding user response：

In Oracle, a DATE is a binary data-type which always has 7 bytes representing century, year-of-century, month, day, hour, minute and second (it is not stored with any format).

Your client application is choosing not to display the time component of the DATE but that does not mean the time component does not exist.

If you use:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Then it may set a different format for the date, if your client application uses the session parameter to control the formatting; if it does not then you will need to adjust the client application's display settings for dates (refer to the manual for your client).

Why aren't the two 24-SEP-21 grouped together?

It is highly likely that they are not grouped together because they have the same date components but different time components and your client application is not showing the time component so you cannot see the difference.

If you want to aggregate by day then TRUNCate the date:

select TRUNC(book_date),
       count(*)
from   rental
group by TRUNC(book_date);