如何使用SQL检查特定时隙中是否存在记录 [英] How to check record exist in particular time slot using SQL

问题描述

你好，



这里我将填写时间表，反对分配给我（用户）的具体行动。

假设用户名是user1和分配给他的活动是A1。现在用户（user1）必须填写A1的时间表，如下所示



Sr.No用户活动日期fromTime totime

1 user1 A1 2018-03-21 12:30 2:30



当user1再次填写时间表然后他/她不应该' t使用时间介于之前的时间和时间之间。





请帮帮我



我尝试过的事情：



这就是我的尝试，

Hello,

Here i am going to fill up timesheet against specific action assigned to me(user).
Suppose user name is user1 and activity assigned to him is A1 .Now user(user1) has to fill up timesheet against A1 like follows

Sr.No User Activity Date fromTime totime
1 user1 A1 2018-03-21 12:30 2:30

When user1 is going to fillup again timesheet then he/she shouldn't use time in between previous fromTime and toTime.


Please help me

What I have tried:

This is what i have tried ,

DECLARE @FROM TIME
SET @FROM='12:30'
select complDate,fromtime,totime from tblTimeSheetMaster where @FROM  between Convert(time,fromtime) and Convert(time,totime) and complDate='2018-03-21'

解决方案

我认为你的问题是你的时间字段不是24小时。在你的样本中，12:15比02:15更晚，所以你的between子句不会按预期工作。在SSMS中运行以下查询并观察结果。

I think your problem is that your time fields are not 24-hour times. In your sample 12:15 is LATER than 02:15, so your between clause will not work as expected. Run the following query in SSMS and observe the result.

declare @start time = '12:15';
declare @complete time = '02:15';
declare @complete2 time = '14:15';
declare @from time = '12:30';

select 1 where @from between @start and @complete;

select 1 where @from between @start and @complete2;

第一个选择语句（使用你现在的12小时时间）将产生一个空数据集，但第二个（使用24小时格式化时间）将返回1。



最后，当完成时间反映第二天时会发生什么？我认为您的架构应该使用 datetime 列而不是单独的日期和时间列。



SQL细微差别不是很有趣吗？

The first select statement (using the 12-hour time you have right now) will yield a null data set, but the second one (using the 24-hour formatted time) will return "1".

Lastly, what happens when the completion time reflects the following day? I think your schema should be using datetime columns instead of separate date and time columns.

Aren't SQL nuances fun?

根据我们的讨论......


检查：

According to our discussion...

Check this:

DECLARE @tblTimeSheetMaster TABLE
(
	Id INT,
	complNo VARCHAR(30),
	complDate DATE,
	complNature VARCHAR(30),
	assignto VARCHAR(30),
	priorit VARCHAR(30),
	[status] VARCHAR(30),
	fromtime TIME,
	totime TIME
)

INSERT INTO @tblTimeSheetMaster(Id, complNo, complDate, complNature, assignto, priorit, [status], fromtime, totime)
VALUES(1, 'B0001/17-18', '2018-03-01', 'Bugs', 'HEMANT', 'High', 'Active', '00:15', '02:15'),
(2, 'B0001/17-18', '2018-03-21', 'Bugs', 'HEMANT', 'High', 'Active', '00:15', '01:15')

DECLARE @t TIME = '00:30'
DECLARE @d DATE = '2018-03-21'
DECLARE @dt DATETIME = CONVERT(DATETIME, @d) + CONVERT(DATETIME, @t)

--SELECT @dt 

SELECT T.*
FROM (
	SELECT Id, complNo, complDate, complNature, assignto, priorit, [status], 
		CONVERT(DATETIME, complDate) + CONVERT(DATETIME, fromtime) AS FromTime1, CONVERT(DATETIME, complDate) + CONVERT(DATETIME, totime) AS ToTime1
	FROM @tblTimeSheetMaster 
) T
WHERE @dt >=T.FromTime1 AND @dt<=T.ToTime1 

结果：

Result:

2	B0001/17-18	2018-03-21	Bugs	HEMANT	High	Active	2018-03-21 00:15:00.000	2018-03-21 01:15:00.000

详情请见： time（Transact-SQL） [ ^ ]

这篇关于如何使用SQL检查特定时隙中是否存在记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！