如何在SQL条件中使用时间从表中获取特定值 [英] How to get particular value from table in SQL using time in where condition

问题描述

我需要使用当前系统时间获取特定的Shiftname

表名：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 

例如我在17:11的输入时间现在输出是S2



以上是我的预期结果但我没有得到答案



我尝试过：



以下是mty查询它不工作



从tblShiftName中选择ShiftName，其中STime in（'17：15'）

解决方案

< blockquote>试试这个：

  SELECT  ShiftName 
 < span class =code-keyword> FROM  tblShiftName 
  WHERE   @ CurrentTime   BETWEEN  STIme  AND  ETime; 

替换 @CurrentTime 具有数据库功能，例如： CONVERT（TIME，GETDATE（））



注意：我建议存储日期和时间部分（ DATETIME 数据类型）。

你是什么需求更接近

  SELECT  ShiftName  FROM  tblShiftName  WHERE  '  17:15'  BETWEEN  STime  AND  ETime 

但即使这样也不理想，甚至可以在所有情况下工作。



问题有两个：首先你使用字符串来存储数值，这意味着所有比较都是使用逐个字符进行的检查所以整个比较结果de只在第一个不同的角色对上。



其次，你的班次在午夜重叠，所以你的结束时间有时会早于你的开始时间。 br />


就个人而言，我会使用一个数值（也许是午夜以来的分钟）并编写一个SP，它会做一个更复杂的检查，只是介于之间您可以使用当前系统做的所有事情。我会查看当前时间并使用它来决定你最有可能进入哪一天，然后检查班次，而不是愚蠢检查。

修复过夜转换相当简单：

  SELECT  
 ShiftName 
  FROM  
 tblShiftName 
  WHERE  
   -  常规班次： 
（STime< ETime 和  @ CurrentTime  之间 STime 和 ETime）
 或 
   -   跨越午夜的转变： 
（STime> ETime 和（ @ CurrentTime > = STi我或  @ CurrentTime < = ETime））
; 

但是，除非所有时间都以UTC格式存储，否则当夏令时的时钟发生变化时，您将遇到问题。当时钟返回时，从 00:31 到 08:30 的转换将会延长工作时间，并且时钟前进时少了一个小时。您需要检查业务规则以决定如何处理此问题。

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')

解决方案

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).

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.

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.

这篇关于如何在SQL条件中使用时间从表中获取特定值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！