Geek Answers Handbook

How to group by DATE only in datetime column

I have a problem with grouping in SQL Server Express 2005

I have a DATETIME COLUMN , but I want to group it only by date.

Here is my SQL expression:

 SELECT (u.FirstName + ' ' + u.LastName) AS [FullName],d.user_id,CONVERT(varchar,d.log_date,101) AS log_date, min(d.login_time) as LOG_IN, max(d.logout_time) as LOG_OUT, sum(d.totaltime) as TOTHrs FROM tbldtr d INNER JOIN tblUsers u ON d.user_id = u.User_Id WHERE d.user_id = 'ADMIN1' and d.log_date BETWEEN '6/1/2013' AND '6/15/2013' GROUP BY DATEADD(day, DATEDIFF(day, 0, log_date), 0),u.FirstName,u.LastName,d.user_id order by d.log_date asc

but he gives me this error:

The column "tbldtr.log_date" is not valid in the selection list because it is not contained in the aggregate function or in the GROUP BY clause.

Thanks in advance.!

+7
sql sql-server
source share
4 answers

Just move convert(varchar,d.log_date,101) to group by :

 select u.FirstName + ' ' + u.LastName as [FullName], d.user_id, convert(varchar, d.log_date, 101) as log_date, min(d.login_time) as LOG_IN, max(d.logout_time) as LOG_OUT, sum(d.totaltime) as TOTHrs from tbldtr d inner join tblUsers u on d.user_id = u.User_Id where d.user_id = 'ADMIN1' and d.log_date between '20130601' AND '20130615' group by convert(varchar, d.log_date, 101), u.FirstName, u.LastName, d.user_id order by log_date asc

In addition, it is safer to change dates in where to an unambiguous format - YYYYMMDD

+5
source share

Use the casting function to become something like:

 GROUP BY CAST(d.log_date AS DATE)
0
source share

if you move convert(varchar,d.log_date,101) to a group, then the result will be incorrect, since it will count the date and time, so when you have more than one record on the same date with different times, it will show twice as a result.

try this it will work

 SELECT ( u.firstname + ' ' + u.lastname ) AS [FullName], d.user_id, CONVERT(VARCHAR, cast(d.log_date as DATE) , 101) AS log_date, Min(d.login_time) AS LOG_IN, Max(d.logout_time) AS LOG_OUT, Sum(d.totaltime) AS TOTHrs FROM tbldtr d INNER JOIN tblusers u ON d.user_id = u.user_id WHERE d.user_id = 'ADMIN1' AND d.log_date BETWEEN '6/1/2013' AND '6/15/2013' GROUP BY cast(d.log_date as DATE), u.firstname, u.lastname, d.user_id ORDER BY d.log_date ASC
0
source share

Try using the following:

 SELECT (u.FirstName + ' ' + u.LastName) AS [FullName],d.user_id,CONVERT(varchar,d.log_date,101) AS log_date, min(d.login_time) as LOG_IN, max(d.logout_time) as LOG_OUT, sum(d.totaltime) as TOTHrs FROM tbldtr d INNER JOIN tblUsers u ON d.user_id = u.User_Id WHERE d.user_id = 'ADMIN1' and d.log_date BETWEEN '6/1/2013' AND '6/15/2013' GROUP BY (u.FirstName + ' ' + u.LastName), d.user_id, CONVERT(varchar,d.log_date,101) order by d.log_date asc
0
source share

More articles:


All Articles