按天排序，忽略小时和分钟

Question

我有个小问题要问你。我想按天排序，但是如果有几个不同时间的在同一天排序，就会把它们分开。我怎么才能知道他每天只露面呢?

我的日期字段是这样的

Datum                         weight
2022-12-09 07:12:49.000       2150
2022-12-09 08:15:49.000       1250

我查询

Select FORMAT(Datum, 'dd/MM/yyyy', 'de-DE') as Datum, SUM(weight)
FROM [Produktionsdaten].[dbo].[produktionedelstahl]
WHERE Anlage IN ('Bonak 2')
AND YEAR(Datum)=YEAR(CURRENT_TIMESTAMP)
AND MONTH(Datum)=MONTH(CURRENT_TIMESTAMP)
group BY (Datum)

我想总结一下这一天

Datum           weight
12-09-2022      3400

Answer 1

我想你已经差不多了——只要改变最后一行

Select FORMAT(Datum, 'dd/MM/yyyy', 'de-DE') as Datum, SUM(weight)
FROM [Produktionsdaten].[dbo].[produktionedelstahl]
WHERE Anlage IN ('Bonak 2')
AND YEAR(Datum)=YEAR(CURRENT_TIMESTAMP)
AND MONTH(Datum)=MONTH(CURRENT_TIMESTAMP)
group BY FORMAT(Datum, 'dd/MM/yyyy', 'de-DE')

Answer 2

您可以尝试用这种方式执行SELECT吗?

SELECT FORMAT(DATE(Datum), 'dd/MM/yyyy', 'de-DE') AS Datum, SUM(weight)
FROM [Produktionsdaten].[dbo].[produktionedelstahl]
WHERE Anlage IN ('Bonak 2')
AND YEAR(Datum)=YEAR(CURRENT_TIMESTAMP)
AND MONTH(Datum)=MONTH(CURRENT_TIMESTAMP)
GROUP BY DATE(Datum)

Answer 3

您可以通过将DATETIME转换为DATE并从那里开始。

SELECT CAST(Datum AS DATE) AS Datum, SUM(Weight) AS Weight
FROM [Produktionsdaten].[dbo].[produktionedelstahl]
GROUP BY CAST(Datum AS DATE)

下面是一个非常小的示例脚本来演示这是如何工作的:

DECLARE @Data TABLE (Datum DATETIME, Weight INT);
INSERT INTO @Data(Datum, Weight) VALUES ('2022-12-09 07:12:49.000', 2150), ('2022-12-09 08:15:49.000', 1250);
SELECT CAST(Datum AS DATE) AS Datum, SUM(Weight) AS Weight
FROM @Data 
GROUP BY CAST(Datum AS DATE)

输出:

<表类>基准面重量tbody><<tr>2022-12-093400