如何为此创建一个SQL查询？（日期不同） [英] how to create a sql query for this ?(date differnce)
本文介绍了如何为此创建一个SQL查询？（日期不同）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
如何计算日期之间的时间。
i有两个表
1.计划问题收到硕士
2.计划提交硕士
- >计划在'2014-01-02 08：30：00.000'向员工发放
- >在这个时间里，这位员工在计划中提交计划'2014-01-04 18：20：00.000'
- >我们的办公时间是10小时
- >我想只计算工作时间。
所以查询怎么可能。
谢谢，
解决方案
请试用此代码。
计算并增加第一天的小时数，最后一天，以及期间的整个日子。
但不使用假期和周末。
此代码也计算小时数，不是分钟。
DECLARE @PlanIssueDate AS datetime
DECLARE @PlanSubmitDate AS datetime
SET @PlanIssueDate ='2014-01-02 08：30：00.000'
SET @PlanSubmitDate ='2014-01-04 18：20：00.000'
SELECT
- 测试
DATEDIFF（DAY，@ PlanIssueDate，@ PlanSubmitDate）AS [TestDays]
，DATEDIFF（HOUR，@ PlanIssueDate，@ PlanSubmitDate）AS [TestHours]
，DATEDIFF（HOUR，DATEADD（HOUR，DATEPART） （HOUR，@ PlanIssueDate），0），DATEADD（HOUR，19,0））AS FirstDayHours
，DATEDIFF（HOUR，DATEADD（HOUR，8,0），DA TEADD（HOUR，DATEPART（HOUR，@ PlanSubmitDate），0））AS LastDayHours
，CASED WHEN DATEDIFF（DAY，@ PlanIssueDate，@ PlanSubmitDate）< 2那么0
ELSE（DATEDIFF（DAY，@ PlanIssueDate，@ PlanSubmitDate） - 1）* 10 - 10小时每天
之间的天数END AS MidDayHours
- 结果
，CASE
WHEN DATEDIFF（DAY，@ PlanIssueDate，@ PlanSubmitDate）= 0 THEN - 在同一天结束
CASE
WHEN DATEDIFF（HOUR，@ PlanIssueDate，@ PlanSubmitDate）> 10那么10
ELSE DATEDIFF（HOUR，@ PlanIssueDate，@ PlanSubmitDate）
END
ELSE - 其他日结束
CASE WHEN DATEDIFF（DAY，@ PlanIssueDate，@ PlanSubmitDate） < 2那么0
ELSE（DATEDIFF（DAY，@ PlanIssueDate，@ PlanSubmitDate） - 1）* 10
END -
+ CASE
WHEN DATEDIFF之间每天10小时（HOUR，DATEADD（HOUR，DATEPART（HOUR，@ PlanIssueDate），0），DATEADD（HOUR，19,0））> 10那么10
ELSE DATEDIFF（小时，DATEADD（小时，DATEPART（小时，@ PlanIssueDate），0），DATEADD（小时，19，0））
结束 - 第一天的小时数
+ CASE
当DATEDIFF（HOUR，DATEADD（HOUR，8,0），DATEADD（HOUR，DATEPART（HOUR，@ PlanSubmitDate），0））> 10那么10
ELSE DATEDIFF（小时，DATEADD（小时，8,0），DATEADD（小时，DATEPART（小时，@ PlanSubmitDate），0））
END - 最后一天的小时数
END AS [小时]
在几分钟内尝试此代码。它更加简单。
DECLARE @PlanIssueDate AS datetime
DECLARE @PlanSubmitDate AS datetime
SET @PlanIssueDate ='2014-01- 02 08：30：00.000'
SET @PlanSubmitDate ='2014-01-04 18：20：00.000'
SELECT
- 测试
DATEDIFF（MINUTE ，@ PlanIssueDate，@ PlanSubmitDate）AS TotalMinutes
，DATEPART（HOUR，@ PlanIssueDate）* 60 + DATEPART（MINUTE，@ PlanIssueDate） - 8 * 60 AS FirstDayMinutes
，CASE WHEN DATEDIFF（DAY，@ PlanIssueDate） ，@ PlanSubmitDate）< 2那么0
ELSE（DATEDIFF（DAY，@ PlanIssueDate，@ PlanSubmitDate） - 1）* 10 * 60
END AS MidDayMinutes
，DATEPART（HOUR，@ PlanSubmitDate）* 60 + DATEPART（ MINUTE，@ PlanSubmitDate） - 8 * 60 AS LastDayMinutes
- 结果
，CASE
当DATEDIFF（DAY，@ PlanIssueDate，@ PlanSubmitDate）= 0那么 - 结束于同一天
CASE
WHEN DATEDIFF（MINUTE，@ PlanIssueDate，@ PlanSubmitDate）> 10那么10 * 60
ELSE DATEDIFF（MINUTE，@ PlanIssueDate，@ PlanSubmitDate）
END
ELSE - 其他日结束
CASE WHEN DATEDIFF（DAY，@ PlanIssueDate，@ PlanSubmitDate）< 2那么0
ELSE（DATEDIFF（DAY，@ PlanIssueDate，@ PlanSubmitDate） - 1）* 10 * 60
END - 每天10小时，
+ CASE
之间的天数当DATEPART（HOUR，@ PlanIssueDate）* 60 + DATEPART（MINUTE，@ PlanIssueDate） - 8 * 60> 600那么600
ELSE DATEPART（HOUR，@ PlanIssueDate）* 60 + DATEPART（MINUTE，@ PlanIssueDate） - 8 * 60
END - 第一天的分钟
+ CASE
当DATEPART（HOUR，@ PlanSubmitDate）* 60 + DATEPART（MINUTE，@ PlanSubmitDate） - 8 * 60> 600那么600
ELSE DATEPART（HOUR，@ PlanSubmitDate）* 60 + DATEPART（MINUTE，@ PlanSubmitDate） - 8 * 60
END - 最后一天的分钟
结束AS [分钟]
Hi ,
How to calculate time in between date.
i have a two table
1. plan issue receive Master
2. Plan Submit Master
-> Plan Issue to employee on '2014-01-02 08:30:00.000'
-> in this one employee submit the plan in plan mater on this time '2014-01-04 18:20:00.000'
-> our office timing is 10 hours
-> i want calculate only working hours time.
so how is it possible in query.
Thank you ,.
解决方案
Try this code, please.
It calculates and adds hours of the first day, last day, and the entire days in the period.
But holidays and weekends are not used.
Also this code calculates hours, not minutes.
DECLARE @PlanIssueDate AS datetime DECLARE @PlanSubmitDate AS datetime SET @PlanIssueDate = '2014-01-02 08:30:00.000' SET @PlanSubmitDate = '2014-01-04 18:20:00.000' SELECT -- Testing DATEDIFF(DAY, @PlanIssueDate, @PlanSubmitDate) AS [TestDays] , DATEDIFF(HOUR, @PlanIssueDate, @PlanSubmitDate) AS [TestHours] , DATEDIFF(HOUR, DATEADD(HOUR, DATEPART(HOUR, @PlanIssueDate), 0), DATEADD(HOUR, 19, 0)) AS FirstDayHours , DATEDIFF(HOUR, DATEADD(HOUR, 8, 0), DATEADD(HOUR, DATEPART(HOUR, @PlanSubmitDate), 0)) AS LastDayHours , CASE WHEN DATEDIFF(DAY, @PlanIssueDate, @PlanSubmitDate) < 2 THEN 0 ELSE (DATEDIFF(DAY, @PlanIssueDate, @PlanSubmitDate) - 1) * 10 -- 10 hours per day for days between END AS MidDayHours -- Result , CASE WHEN DATEDIFF(DAY, @PlanIssueDate, @PlanSubmitDate) = 0 THEN -- ends in the same day CASE WHEN DATEDIFF(HOUR, @PlanIssueDate, @PlanSubmitDate) > 10 THEN 10 ELSE DATEDIFF(HOUR, @PlanIssueDate, @PlanSubmitDate) END ELSE -- ends on other day CASE WHEN DATEDIFF(DAY, @PlanIssueDate, @PlanSubmitDate) < 2 THEN 0 ELSE (DATEDIFF(DAY, @PlanIssueDate, @PlanSubmitDate) - 1) * 10 END -- 10 hours per day for days between + CASE WHEN DATEDIFF(HOUR, DATEADD(HOUR, DATEPART(HOUR, @PlanIssueDate), 0), DATEADD(HOUR, 19, 0)) > 10 THEN 10 ELSE DATEDIFF(HOUR, DATEADD(HOUR, DATEPART(HOUR, @PlanIssueDate), 0), DATEADD(HOUR, 19, 0)) END -- Hours for the first day + CASE WHEN DATEDIFF(HOUR, DATEADD(HOUR, 8, 0), DATEADD(HOUR, DATEPART(HOUR, @PlanSubmitDate), 0)) > 10 THEN 10 ELSE DATEDIFF(HOUR, DATEADD(HOUR, 8, 0), DATEADD(HOUR, DATEPART(HOUR, @PlanSubmitDate), 0)) END -- Hours for the last day END AS [Hours]
Try this code in minutes. It is much simpler.
DECLARE @PlanIssueDate AS datetime DECLARE @PlanSubmitDate AS datetime SET @PlanIssueDate = '2014-01-02 08:30:00.000' SET @PlanSubmitDate = '2014-01-04 18:20:00.000' SELECT -- Testing DATEDIFF(MINUTE, @PlanIssueDate, @PlanSubmitDate) AS TotalMinutes , DATEPART(HOUR, @PlanIssueDate) * 60 + DATEPART(MINUTE, @PlanIssueDate) - 8 * 60 AS FirstDayMinutes , CASE WHEN DATEDIFF(DAY, @PlanIssueDate, @PlanSubmitDate) < 2 THEN 0 ELSE (DATEDIFF(DAY, @PlanIssueDate, @PlanSubmitDate) - 1) * 10 * 60 END AS MidDayMinutes , DATEPART(HOUR, @PlanSubmitDate) * 60 + DATEPART(MINUTE, @PlanSubmitDate) - 8 * 60 AS LastDayMinutes -- Result , CASE WHEN DATEDIFF(DAY, @PlanIssueDate, @PlanSubmitDate) = 0 THEN -- ends in the same day CASE WHEN DATEDIFF(MINUTE, @PlanIssueDate, @PlanSubmitDate) > 10 THEN 10 * 60 ELSE DATEDIFF(MINUTE, @PlanIssueDate, @PlanSubmitDate) END ELSE -- ends on other day CASE WHEN DATEDIFF(DAY, @PlanIssueDate, @PlanSubmitDate) < 2 THEN 0 ELSE (DATEDIFF(DAY, @PlanIssueDate, @PlanSubmitDate) - 1) * 10 * 60 END -- 10 hours per day for days between + CASE WHEN DATEPART(HOUR, @PlanIssueDate) * 60 + DATEPART(MINUTE, @PlanIssueDate) - 8 * 60 > 600 THEN 600 ELSE DATEPART(HOUR, @PlanIssueDate) * 60 + DATEPART(MINUTE, @PlanIssueDate) - 8 * 60 END -- Minutes for the first day + CASE WHEN DATEPART(HOUR, @PlanSubmitDate) * 60 + DATEPART(MINUTE, @PlanSubmitDate) - 8 * 60 > 600 THEN 600 ELSE DATEPART(HOUR, @PlanSubmitDate) * 60 + DATEPART(MINUTE, @PlanSubmitDate) - 8 * 60 END -- Minutes for the last day END AS [Minutes]
这篇关于如何为此创建一个SQL查询？（日期不同）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
