如果换档开始时间是11PM - 结束时间是3AM,如何计算换档时间 [英] How to calclate shift time if shift start time is 11PM - and end time is 3AM
问题描述
我的任务从2014年6月27日晚上11:20开始我现在想知道它发生在晚上11点 - 凌晨3点。但是2AM在第二天,所以我的结果不正确。
SELECT * FROM TBL_TASK
AND CONVERT(VARCHAR,TBL_TASK.StartDateTime,108)> = CAST(转换(char(5),cast('11PM'AS datetime),108)AS time)
AND CONVERT (VARCHAR,TBL_TASK.StartDateTime,108)< = CAST(转换(char(5),cast('2AM'AS datetime),108)AS time)
首先要做的事情是:永远不要将日期作为字符串进行比较:它们总是进行基于字符串的比较,这些比较是逐个字符的,并且在第一个不同的字符处停止。
您试图比较时间,为什么要扔掉信息并导致潜在问题?
相反,取出开始日期,扔掉时间部分,并在班次的开始和结束时抵消它。
SELECT * FROM Tbl_Task
WHERE StartDateTime BETWEEN
DATEADD(hh, 23 ,CAST(CAST(StartDateTime AS DATE ) AS DATETIME ))
AND
DATEADD(hh,< span class =code-digit> 23 + 4 ,CAST(CAST(StartDateTime AS DATE ) AS DATETIME ))
I have a task which start from 27/06/2014 11:20PM now I want to find out it occurs in 11PM - 3AM shift. but 2AM is on 2nd day so my result is not coming correctly.
SELECT * FROM TBL_TASK
AND CONVERT(VARCHAR, TBL_TASK.StartDateTime, 108) >= CAST(convert(char(5),cast('11PM' AS datetime),108) AS time)
AND CONVERT(VARCHAR, TBL_TASK.StartDateTime, 108) <= CAST(convert(char(5),cast('2AM' AS datetime),108) AS time)
First things first: never compare dates as strings: they always do string based comparisons which are character by character and which stop at the first character which is different.
You are trying to compare times, so why throw away information and cause potential problems?
Instead, take the start date, throw away the time part, and offset it by the start and end of the shift.
SELECT * FROM Tbl_Task WHERE StartDateTime BETWEEN DATEADD(hh, 23, CAST(CAST(StartDateTime AS DATE)AS DATETIME)) AND DATEADD(hh, 23 + 4, CAST(CAST(StartDateTime AS DATE)AS DATETIME))
这篇关于如果换档开始时间是11PM - 结束时间是3AM,如何计算换档时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!