I'm trying to write this query to retrieve No. of records grouped by date:
SELECT system_date,count (is_paid)
FROM TPWEB.TP_CLIENT_TENDER_TRANS
where system_date between '1-DEC-12' and '31-DEC-12'
group by system_date
But, I got result without grouping such as:
01-DEC-12 1
01-DEC-12 1
01-DEC-12 1
01-DEC-12 1
what is wrong...
As is documented the DATE datatype is a datetime and thus stores the hour, minute and second in addition to the attributes you'd, more reasonably expect, in a datatype with this name. If you want to count over a day you need to remove the time portion of your date. It is the default behaviour of TRUNC(), so to do this trunc(<date>) is all you need.
It's worth noting two things at this point:
I'm assuming system_date is a column in your table and not a misunderstanding of SYSDATE
Your between clause is completely incorrect, dangerously so.
By the way your dates have been represented it appears as though your NLS_DATE_FORMAT is DD-MON-YYYY (see another answer of mine for more details). This means that when you implicitly convert a date into a character it is converted in this format.
You're not using either a datetime literal or an explicit conversion of the values you're comparing to, which means your date is being implicitly converted to a character. However, when you do the comparison you'll find that things aren't always as they seem. Character comparison is, normally, binary; this means that the 10th of February is not between the 10th January and the 10th March; "March" is smaller than "January".
Always explicitly convert dates and always use dates when doing date comparisons.
Putting all of this together your query becomes:
select trunc(system_date), count(is_paid)`
from TPWEB.TP_CLIENT_TENDER_TRANS
where system_date between date '2012-12-01' and date '2012-12-31'
group by trunc(system_date)
Use trunc function, the DATE datatype holds the time with it, so using trunc we can truncate time. Below query works!
SELECT trunc(system_date),count (is_paid)
FROM TPWEB.TP_CLIENT_TENDER_TRANS
where system_date between '1-DEC-12' and '31-DEC-12'
group by trunc(system_date);
DATE columns in Oracle contain precision up to milliseconds, not just the date. If you're only interested in the date itself, you should TRUNC it to remove hours, minutes, etc.:
SELECT system_date,COUNT (is_paid)
FROM TPWEB.TP_CLIENT_TENDER_TRANS
WHERE system_date BETWEEN '1-DEC-12' AND '31-DEC-12'
GROUP BY TRUNC(system_date, 'DD-MON-YY')
DATE is represented by 7 bytes consisting of 1-byte each for century, year-of-century, month, day, hour, minute and seconds; it only stores integer seconds (not milli-seconds). If you have fractional seconds then you have a TIMESTAMP data type which can store between 0 and 9 decimal digits for the seconds (depending on if you use TIMESTAMP(0) or TIMESTAMP(9) or something in between).This works for me:
select DT_FECHA_DESDE,DT_FECHA_HASTA
from (SELECT TRUNC(DT_FECHA_DESDE)as DT_FECHA_DESDE,TRUNC(DT_FECHA_HASTA) as DT_FECHA_HASTA
FROM TBL1
WHERE run='3456'
order by DT_FECHA_DESDE)
group by DT_FECHA_DESDE,DT_FECHA_HASTA
SELECT system_date, is_paid FROM TPWEB.TP_CLIENT_TENDER_TRANS where system_date between '1-DEC-12' and '31-DEC-12'