如何编写SQL查询以查找给定时间是否介于开始时间和结束时间之间 [英] How to write a sql query to find whether given time lies in between start time and end time

问题描述

我需要根据条件获取表中的行.
条件是:

位于开始时间和结束时间之间的行.

我已经尝试过，但是我在时间6/9/2010 10:43:04 AM中得到了三个值


6/9/2010 6/9/2010 10:42:04 AM 6/9/2010 10:43:11 AM 4
6/9/2010 6/9/2010 10:43:11 AM 6/9/2010 10:43:47 AM 5
6/9/2010 6/9/2010 10:43:47 AM 6/9/2010 10:44:54 AM 4


请help

I need to get the rows in a table according to a condtion.
The condition is :

row that lies between starttime and end time.

I have tried,But i got three values for time 6/9/2010 10:43:04 AM


6/9/2010 6/9/2010 10:42:04 AM 6/9/2010 10:43:11 AM 4
6/9/2010 6/9/2010 10:43:11 AM 6/9/2010 10:43:47 AM 5
6/9/2010 6/9/2010 10:43:47 AM 6/9/2010 10:44:54 AM 4


Pls help

推荐答案

chinnu20写道:

chinnu20 wrote:

位于开始时间和结束时间之间的行.

row that lies between starttime and end time.

希望您的意思是仅时间，而不是完整的日期时间(因为那太简单了！)

试试这个:

Hope you meant time only and not full datetime (as that would be too simple!)

Try this:

--Explanation:
--if you have varchar(5) you will obtain HH:mm
--if you have varchar(8) you obtain HH:mm ss
--108 obtains only the time from the SQL date
--@leftTime and @rightTime are two variables to compare
CONVERT(varchar(5), thedate, 108) between @leftTime and @rightTime

另一种执行此操作的方法: http://www.vijaykodali.com/Blog/post/2008/05/14/Comparing-Time-part-in-DateTime-SQL.aspx [ MSDN:DATEPART(Transact-SQL) [

Another way to do the same: http://www.vijaykodali.com/Blog/post/2008/05/14/Comparing-Time-part-in-DateTime-SQL.aspx[^]


If needed, you can also use DATEPART.
Refer: MSDN: DATEPART (Transact-SQL)[^]

这篇关于如何编写SQL查询以查找给定时间是否介于开始时间和结束时间之间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！