Getting rid of the time in a date

Question

SELECT close_date, close_UIN, count(*) as Amount_Closed_per_employee
FROM   t_return_master
where status = 'C' AND TO_DATE (close_date ,'DD-MON-YY') BETWEEN TO_DATE     
('~Date From~', 'DD/MM/YYYY') AND TO_DATE('~Date To~','DD/MM/YYYY') 
group by close_date, close_UIN
order by close_UIN

I have the code you see above, at the moment it displays the hours, minutes and seconds when it displays the close date, however as I am grouping by close date, I need this to go so it group by date only. Anyone any ideas how I can do this?

`TO_DATE (close_date ,'DD-MON-YY')` is wrong. You should never use `TO_DATE` on a DATE. It will first convert it to string based on locale-specific NLS_DATE_FORMAT and then back to DATE. See http://stackoverflow.com/a/29559609/3989608 — Lalit Kumar B, May 01 '15 at 09:22

score 2 · Answer 1 · answered May 01 '15 at 08:35

You can use TRUNC() on the date (in the SELECT and GROUP BY clauses) which, with just the default argument, sets the hours/minutes/seconds/milliseconds to zero.

SELECT TRUNC( close_date ) AS close_date_day,
       close_UIN,
       count(*) as Amount_Closed_per_employee
FROM   t_return_master
where  status = 'C'
AND    TO_DATE (close_date ,'DD-MON-YY')
         BETWEEN TO_DATE('~Date From~', 'DD/MM/YYYY')
         AND     TO_DATE('~Date To~','DD/MM/YYYY') 
group by TRUNC( close_date ), close_UIN
order by close_UIN

Alex Poole · Answer 2 · 2015-05-01T09:20:31.997

The simplest way is to use the trunc() function, which by default sets the time portion of the date value to midnight:

SELECT trunc(close_date), close_UIN, ...
... 
group by trunc(close_date), close_UIN
order by close_UIN

Note that you need to truncate the value in the select list and the group-by clause. The function can optionally truncate to a different precision too, like removing seconds, or going to the first day of the month, etc.:

select sysdate, trunc(sysdate), trunc(sysdate, 'SS') from dual;

SYSDATE             TRUNC(SYSDATE)      TRUNC(SYSDATE,'MI')
------------------- ------------------- -------------------
2015-05-01 09:37:48 2015-05-01 00:00:00 2015-05-01 09:37:00

See the docs for more info.

This is not correct:

AND TO_DATE (close_date ,'DD-MON-YY') BETWEEN TO_DATE     
('~Date From~', 'DD/MM/YYYY') AND TO_DATE('~Date To~','DD/MM/YYYY')

Since close_date is already a date column you're doing an implicit conversion to a string using your NLS_DATE_FORMAT, and then an explicit conversion back to a date using 'DD-MON-YY' - which is using a two-digit year and will cause its own problems. The behaviour may vary for other users with different settings.

It looks like you're trying to do the equivalent of a trunc() to make the between cover the whole of the final day, but you won't get the year (century) you expect. And calling any function on the column will prevent any index on it being used, unless you have a matching function-based index (but using trunc() only in the select list and group by is OK).

If you want all records from the start of 'date from' to the end of 'date to' you can specify that with a range instead of 'between' - which is inclusive but might not get the result you expect depending on your NLS settings, again. Something like:

AND close_date >= TO_DATE('~Date From~', 'DD/MM/YYYY')
AND close_date < TO_DATE('~Date To~','DD/MM/YYYY') + 1

Less than midnight on the day after 'date to' is equivalent to saying up to 23:59:59 on 'date to', i.e. covering that entire day.

Getting rid of the time in a date

2 Answers2