SQLServerGROUPBY 日期时间忽略小时分钟和带有日期和和值的选择

我有一个有两个字段的表—— datetime和 int。我想做一个小组的 datetime只有在日期忽略的时间和分钟。SELECT语句应该返回一个日期，该日期映射为一天的整数之和。

161985 次浏览

小开

SELECT CAST(Datetimefield AS DATE) as DateField, SUM(intfield) as SumField
FROM MyTable
GROUP BY CAST(Datetimefield AS DATE)

小开

As he didn't specify which version of SQL server he uses (date type isn't available in 2005), one could also use

SELECT CONVERT(VARCHAR(10),date_column,112),SUM(num_col) AS summed
FROM table_name
GROUP BY CONVERT(VARCHAR(10),date_column,112)

小开

-- I like this as the data type and the format remains consistent with a date time data type

;with cte as(
select
cast(utcdate as date) UtcDay, DATEPART(hour, utcdate) UtcHour, count(*) as Counts
from dbo.mytable cd
where utcdate between '2014-01-14' and '2014-01-15'
group by
cast(utcdate as date), DATEPART(hour, utcdate)
)
select dateadd(hour, utchour, cast(utcday as datetime)) as UTCDateHour, Counts
from cte

小开

I came researching the options that I would have to do this, however, I believe the method I use is the simplest:

SELECT COUNT(*),
DATEADD(dd, DATEDIFF(dd, 0, date_field),0) as dtgroup
FROM TABLE
GROUP BY DATEADD(dd, DATEDIFF(dd, 0, date_field),0)
ORDER BY dtgroup ASC;

小开

Personally i prefer the format function, allows you to simply change the date part very easily.

     declare @format varchar(100) = 'yyyy/MM/dd'
select
format(the_date,@format),
sum(myfield)
from mytable
group by format(the_date,@format)
order by format(the_date,@format) desc;