Group By by Date

Question

Group By by Date

Navigation

#1 by (4 votes)
#2 by (2 votes)

2

I have a table named tsc , with the properties ID and DataHoraOcorrencia . I need to query the amount of ID and DataHoraOcorrecia .

SQL

SELECT 
    DataHoraOcorrencia as DataHora, 
    COUNT(tsc.ID) as Quantidade 
    FROM tsc 
    WHERE DataHoraOcorrencia BETWEEN '2017-07-01' AND '2017-07-30';

My problem is in the form of DataHoraOcorrecia , because if I make a between with the date in aaaa-mm-dd format, it brings me with the hours, and I only need it per day. In the case it groups by date and time and not just by date.

The DataHoraOcorrencia ed entity is in aaaa-mm-dd hh:ii:ss format.

mysql sql datetime date

asked by anonymous 10.11.2017 / 14:13

2 answers

2

Following is an example, used in SQL Sever

Declare @tabela as table(DataHoraOcorrencia datetime, Id int)
insert into @tabela (DataHoraOcorrencia, Id)
Values     
('10-07-2017 12:48:37',1),
('09-07-2017 10:40:30',2),
('10-07-2017 12:40:30',3),
('09-07-2017 12:30:30',4),
('10-07-2017 11:00:30',5)


SELECT 
    convert(Date,a.DataHoraOcorrencia) as DataHora, 
    COUNT(a.ID) as Quantidade 
    From @tabela a
    Where convert(Date,DataHoraOcorrencia) BETWEEN '2017-07-01' AND '2017-07-30'
   Group by convert(Date,a.DataHoraOcorrencia)

10.11.2017 / 15:53

Can a desktop program be considered client-side? Apply CSS to a button

score 4 · Accepted Answer

Doing SQL in this way already resolves (just by adding the date () function before the date field:

SELECT 
    DataHoraOcorrencia as DataHora, 
    COUNT(tsc.ID) as Quantidade 
    FROM tsc 
    WHERE Date(DataHoraOcorrencia) BETWEEN '2017-07-01' AND '2017-07-30'
 GROUP BY DataHoraOcorrencia;