SQL Server查询——计数不同的DateTime字段

Supposing we have the following records in an SQL Server table.

假设SQL Server表中有以下记录。

Date
19/5/2009 12:00:00 pm
19/5/2009 12:15:22 pm
20/5/2009 11:38:00 am

What is the SQL syntax for getting something like this one?

得到这样的东西的SQL语法是什么?

Date Count
19/5/2009 2
20/5/2009 1

日期计19/5/2009 2 20/5/2009 1

5 个解决方案

#1

You need to do any grouping on a Date only version of your datefield, such as this.

您需要在您的datefield的一个日期版本上进行任何分组，比如这个。

SELECT
    CONVERT(VARCHAR(10), YourDateColumn, 101),
    COUNT(*)
FROM
    YourTable
GROUP BY
    CONVERT(VARCHAR(10), YourDateColumn, 101)

I usually do this though, as it avoids conversion to varchar.

我通常这样做，因为它避免了转换为varchar。

SELECT
    DATEPART(yy, YourDateColumn),
    DATEPART(mm, YourDateColumn),
    DATEPART(dd, YourDateColumn),
    COUNT(*)
FROM
    YourTable
GROUP BY
    DATEPART(yy, YourDateColumn),
    DATEPART(mm, YourDateColumn),
    DATEPART(dd, YourDateColumn)

EDIT: Another way to get just the date part of a datetime

编辑:获取日期时间的日期部分的另一种方法

DATEADD(d, 0, DATEDIFF(d, 0, YourDateColumn))

#2

That would depend on your database engine. For SQL Server 2008 (and future versions), you can use the date type to do this.

这取决于您的数据库引擎。对于SQL Server 2008(以及将来的版本)，您可以使用date类型来实现这一点。

select
    convert(date, date_column_name) as Date,
    count(1) as Count

from table_name

group by convert(date, date_column_name)

#3

Depends on your DBMS. Example for Mysql:

取决于你的DBMS。Mysql的示例:

SELECT DATE_FORMAT(dateColumn, '%e/%c/%Y') as `date`, COUNT(*)
FROM YourTable
GROUP BY `date`

#4

What RDBMS are you on? Using Sybase, your query would look like this:

你在做什么RDBMS ?使用Sybase，您的查询将如下所示:

select date(datetimeColumn) as myDate, count(*) as myTotal
from thisTable
Group by myDate
Order by myTotal, myDate

#5

After Googling found this one too...

在谷歌上也找到了这个……

SELECT CAST(FLOOR(CAST(Expr1 AS FLOAT)) AS DATEtime) AS Expr1,

选择CAST(FLOOR(CAST) (Expr1作为FLOAT)))作为DATEtime，选择Expr1作为Expr1，

COUNT(*) AS Expr2

Expr2 COUNT(*)

FROM MY_TABLE

从MY_TABLE

GROUP BY

集团

CAST(FLOOR(CAST(Expr1 AS FLOAT)) AS DATEtime)

CAST(FLOOR(CAST) (Expr1作为FLOAT))

The cons?

缺点呢?

High speed execution
高速执行
The results returned are in the original locale. Ex for Greek 19/5/2009
返回的结果位于原始区域。希腊19/5/2009交货

Thank you all

谢谢大家

#1