CODEWITHSUNDEEP
sqlsql-servert-sql
rayncorg
rayncorg

Reputation: 993

How to group by DATE only in column datetime

I'm having a problem with grouping in SQL Server Express 2005

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

Here my SQL Statement:

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 it give me this error:

Column 'tbldtr.log_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Thanks in advance.!

Upvotes: 7

Views: 26438

Answers (4)

KrazzyNefarious
KrazzyNefarious

Reputation: 3230

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

Upvotes: 0

roman
roman

Reputation: 117606

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

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

Also, it's more safe to change dates in the where into unambiguous format - YYYYMMDD

Upvotes: 5

Dhaval
Dhaval

Reputation: 2861

if you move convert(varchar,d.log_date,101) in group by then result will not be right as it will count both date and time so when you have more then one entry in same date with different time it will show two time in 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

Upvotes: 0

Chevul Ervin
Chevul Ervin

Reputation: 883

Use the casting function so it should become something like:

GROUP BY CAST(d.log_date AS DATE)

Upvotes: 0

Related Questions