如何今天和第二天作为一天？

本文介绍了如何今天和第二天作为一天？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个生产模块，有两个班次，一个是白班，另一个是夜班，我想得到白天和夜班的总产量 Ex。 12/02/2016班次当天上午8:00:00至晚上8:00:00 400Kgs生产 12/022016班次晚上8:00:00 PM至13/02/2016 8 ：00：00 AM 600Kgs生产 i希望按照我的班次获得12/02/2016 1000kgs的总产量 i对此没有任何想法，请回复，如果有的话？ 班次时间固定 日：8 ：00：00至8:00:00 Pm 晚上：8：00：00 Pm至次日8:00:00 Am i将运行sql查询以获得按日期生成的总产量 查询将如下所示：

I have a production module which has two shift one is day shift and other is night shift,I want to get total production of day and night shift For Ex. 12/02/2016 shift day 8:00:00 AM to 8:00:00 PM 400Kgs production 12/022016 Shift night 8:00:00 PM to 13/02/2016 8:00:00 AM 600Kgs production i want to get total production of 12/02/2016 1000kgs as per my shift i don't have any idea about this, please reply if any? shift time is fixed Day :8:00:00 Am To 8:00:00 Pm Night :8:00:00 Pm To Next day 8:00:00 Am i will run sql query to get total production by date query will be soming like this:

select sum(tot_qty) from production where date="12/02/2016";

它将返回两班总生产量为i如上所述。 我有什么特里d： i对此没有任何想法，请帮忙！

it will return two shift total production as i described above. What I have tried: i don't have any idea about this, please help!

推荐答案

我会尝试 I would try CREATE TABLE #testprod (id INT IDENTITY(1,1), [date] DATETIME, tot_qty FLOAT) INSERT INTO #testprod ([date], tot_qty) VALUES ( DATETIMEFROMPARTS(2016,2,12, 9,0,0,0), 100) INSERT INTO #testprod ([date], tot_qty) VALUES ( DATETIMEFROMPARTS(2016,2,12, 11,0,0,0), 300) INSERT INTO #testprod ([date], tot_qty) VALUES ( DATETIMEFROMPARTS(2016,2,12, 22,0,0,0), 200) INSERT INTO #testprod ([date], tot_qty) VALUES ( DATETIMEFROMPARTS(2016,2,13, 07,59,0,0), 400) SELECT dateadd(day,datediff(day,0, DATEADD(hour, -8, date ) ),0) as day , SUM(TOT_QTY) FROM #testprod GROUP BY dateadd(day,datediff(day,0, DATEADD(hour, -8, date ) ),0)

the

dateadd(day,datediff(day,0, date ),0)

将对di的所有记录进行分组白天的不同时间 和

will group all records of different times by day and the

DATEADD(hour, -8, date )

会将记录的实际时间移动8小时，以便它现在匹配一天的正常边界

will 'move' the actual time of the record back by 8 hours, so that it now matches the 'normal' boundaries of a day

SELECT SUM(TOT_QTY) FROM PRODUCTION WHERE DATE BETWEEN '12/02/2016 08:00:00' AND '13/02/2016 07:59:59'