sql – 如何仅在列datetime中按DATE分组

发布时间：2020-12-12 07:06:47 所属栏目：MsSql教程来源：网络整理

导读：我在SQL Server Express 2005中进行分组时遇到问题我有一个DATETIME COLUMN,但我只想按日期分组. 这是我的SQL语句： SELECT (u.FirstName + ' ' + u.LastName) AS [FullName],d.user_id,CONVERT(varchar,d.log_date,101) AS log_date,min(d.login_time) as L

我在SQL Server Express 2005中进行分组时遇到问题

我有一个DATETIME COLUMN,但我只想按日期分组.

这是我的SQL语句：

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,log_date),0),u.FirstName,u.LastName,d.user_id order by d.log_date asc

但它给了我这个错误：

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.

提前致谢.！

解决方法

只需将convert(varchar,101)移动到group by子句中：

select
    u.FirstName + ' ' + u.LastName as [FullName],convert(varchar,101) as log_date,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,101),d.user_id
order by log_date asc

此外,将其中的日期更改为明确的格式–YYYYMMDD更安全

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!