How to check record exist in particular time slot using SQL

Question

0.00/5 (No votes)

See more:

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'

Posted 20-Mar-18 22:38pm

SujataJK

Updated 20-Mar-18 23:51pm

Add a Solution

Comments

Maciej Los 21-Mar-18 4:48am

And what's wrong with your code?

SujataJK 21-Mar-18 5:01am

In my table this is record set

Id complNo complDate complNature assignto priorit status fromtime totime

1. B0001/17-18 2018-03-1 Bugs HEMANT High Active 12:15 02:15
2. B0001/17-18 2018-03-21 Bugs HEMANT High Active 12:15 01:15

According to my query result will be

2. B0001/17-18 2018-03-21 Bugs HEMANT High Active 12:15 01:15
but it does not show any record.

Maciej Los 21-Mar-18 5:05am

Do you store [FromTime] And [ToTime] as a text?

SujataJK 21-Mar-18 5:07am

no .
datatype of fromTime and totime is Time

Maciej Los 21-Mar-18 5:36am

Take a look at your input data...
FromTime: 12:15
ToTime: 01:15
You're looking for 12:30
Depending on system settings, 01:15 is less than 12:15, because 01:15 is after midnight...
Got it?
I'd suggest to store these two values as a DateTime data type.

SujataJK 21-Mar-18 5:42am

yes you are right.I got my mistake thanks a lot.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Accepted Answer · 2018-03-20T23:36:00

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.

SQL

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;

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?

Maciej Los · Accepted Answer · 2018-03-20T23:52:00

According to our discussion...

Check this:

SQL

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

For further details, please see: time (Transact-SQL)[^]

How to check record exist in particular time slot using SQL

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0