Click here to Skip to main content
14,086,389 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
I need to get particular Shiftname using only current system time
Table Name:tblShiftName
ID    ShiftName STime           ETime
1     S1        08:30:00        16:30:00
2     S2        16:31:00        00:30:00
3     S3        00:31:00        08:30:00

For example my input time in 17:11 Now the output is S2

The above is my expected result but i had'nt get that answer

What I have tried:

The following is mty query its not working

Select ShiftName from tblShiftName where STime in('17:15')
Posted
Updated 26-Feb-19 6:17am
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Try this:

SELECT ShiftName
FROM tblShiftName
WHERE @CurrentTime BETWEEN STIme AND ETime;


Replace @CurrentTime with database function, such as: CONVERT (TIME, GETDATE())

Note: i'd suggest to store date and time parts (DATETIME data type).
   
Comments
OriginalGriff 25-Feb-19 7:43am
   
Um.
You did notice one shift goes over midnight?
Maciej Los 25-Feb-19 7:53am
   
Yes.
BTW: That's why i suggest to use date-and-time data type instead just a time data type.
Flower@12 26-Feb-19 0:42am
   
Its Working Thank u sir But i've used
@CurrentTime1=CAST(CONVERT(varchar(20), GETDATE(), 108) AS DateTime) Because i'm using sql server 2005
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

What you need is closer to
SELECT ShiftName FROM tblShiftName WHERE '17:15' BETWEEN STime AND ETime
But even that isn't going to be ideal, or even work in all cases.

The problem is twofold: firstly you are using strings to store numeric values which means that all comparisons are performed using character-by-character checks so the result of the whole comparison depends only on the first different character pair.

And secondly, your shifts overlap midnight, so your "end time" is sometimes earlier than your "start time".

Personally, I'd use a numeric value ("minutes since midnight" perhaps) and write an SP which does a more complicated check that just "between" which is about all you can do with the current system. I'd look at the current time and use that to decide which day you are most likely to be in, then check for shift, rather than a "dumb" check.
   
Comments
Flower@12 26-Feb-19 3:09am
   
Pls help the query works only The below Times
08:30 To 16:30 & 01:30 To 08:30
But not working for 16:30 to 00:30

For example
My input is '17:15' It does not return any rows what is the problem?
OriginalGriff 26-Feb-19 3:19am
   
Quote:
"And secondly, your shifts overlap midnight, so your "end time" is sometimes earlier than your "start time"."

That's why you need to be a lot more sophisticated than "it's between this and this".
Flower@12 26-Feb-19 4:42am
   
If Anyother is possible pls help me sir
OriginalGriff 26-Feb-19 4:50am
   
Think about the data you have, and how you'd do it manually.

You are given a time. How do you, yourself, decide which shift it is in? Ignore computers for the moment.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

Fixing the overnight shift is fairly simple:
SELECT 
    ShiftName 
FROM 
    tblShiftName 
WHERE 
    -- Regular shift:
    (STime < ETime And @CurrentTime Between STime And ETime)
Or 
    -- Shift which spans midnight:
    (STime > ETime And (@CurrentTime >= STime Or @CurrentTime <= ETime))
;

However, unless all times are stored in UTC, you'll have issues when the clock changes for Daylight Saving Time. The shift from 00:31 to 08:30 will end up working an extra hour when the clocks go back, and an hour less when the clocks go forward. You'll need to check the business rules to decide how to handle this.
   
Comments
Maciej Los 26-Feb-19 12:41pm
   
5ed!
Flower@12 27-Feb-19 23:45pm
   
how to set Same Date Between if Today's morning time 08:30 AM to Next Day Morning 08:30 AM
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

What you are looking for is the BETWEEN operation.
BETWEEN (Transact-SQL) - SQL Server | Microsoft Docs[^]

What you should know is that your data should probably be adjusted; the 1 minute differences between a shift ending and the next shift starting will return an empty recordset
SELECT  ShiftName
FROM    tblShiftName
WHERE   '17:15' BETWEEN STime AND ETime
   

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01 | 2.8.190518.1 | Last Updated 26 Feb 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100