从数据库中过滤时间 [英] Filter Time from Database
问题描述
嗨
i希望过滤我有记录的时间
i写了这样的查询查询将获取介于12:00 Pm和05:00之间的时间Pm
从#temp中选择cs_ClinicName,FromTime,ToTime
WHERE(1 = 1)和
(CONVERT之间的FromTime(时间,'12:00')和CONVERT (时间,'17:00')
或
CONVERT(时间,'12:00')和CONVERT(时间,'17:00')之间的时间间隔)
但它仅过滤此记录< pre lang =css>
Micolaj11111 09:00:00.0000000 13:00 :00.0000000
我期待的是它必须给所有工作的诊所
< pre lang =css>介于12之间:00 Pm和05:00 Pm
根据记录在该时段之间的所有临床工作(< pre lang =css> 12:00 Pm和05:00 Pm)
Clinic NAME FromTime ToTime
Boyka 09:00:00.0000000 18:00:00.0000000
诊所名称10:00:00.0000000 18:00:00.0000000
sleek clini 10:00:00.0000000 18:00:00.0000000
Life Clinic 10:00:00.0000000 18:00:00.0000000
SAiClini 09:00:00.0000000 18:00:00.0000000
jh 10:00:00.0000000 19:00:00.0000000
Micolaj11111 09:00:00.0000000 13:00:00.0000000
牙科诊所10:00:00.0000000 19:00:00.0000000
眼科诊所09:00:00.0000000 18:00:00.0000000
ryan 01:00:00.0000000 06:00:00.0000000
< pre>
结果正确返回,因为只有记录在12:00到17:00之间。如果您想要在12:00至17:00之间开放诊所,请尝试此查询。将@a替换为您的表名。
选择 CONVERT (时间,' 12:00'), CONVERT ( Time ,' 17:00'),ClinicName,FromTime,ToTime from @ a
WHERE (1 = 1)< span class =code-keyword>和
((FromTime< = CONVERT (时间,' 12:00')和 ToTime> = CONVERT (时间,' 12:00'))
或
(FromTime< = CONVERT (时间,' 17 :00')和 ToTime> = CONVERT (时间,' 17:00'))
Hi
i want to filter time i have records
i wrote query like this query will fetch time between 12:00 Pm and 05:00 Pm
Select cs_ClinicName , FromTime ,ToTime from #temp
WHERE (1=1) and
( FromTime between CONVERT(Time,'12:00') and CONVERT(Time,'17:00')
or
ToTime between CONVERT(Time,'12:00') and CONVERT(Time,'17:00') )
But it is filtering only this records <pre lang="css">
Micolaj11111 09:00:00.0000000 13:00:00.0000000
what i a m expecting is i t must give all clinic who work between
<pre lang="css">between 12:00 Pm and 05:00 Pm
according to the records all clinic work between that time slot(<pre lang="css">12:00 Pm and 05:00 Pm)
Clinic NAME FromTime ToTime Boyka 09:00:00.0000000 18:00:00.0000000 Clinic Name 10:00:00.0000000 18:00:00.0000000 sleek clini 10:00:00.0000000 18:00:00.0000000 Life Clinic 10:00:00.0000000 18:00:00.0000000 SAiClini 09:00:00.0000000 18:00:00.0000000 jh 10:00:00.0000000 19:00:00.0000000 Micolaj11111 09:00:00.0000000 13:00:00.0000000 Dental Clinic 10:00:00.0000000 19:00:00.0000000 Eye clinic 09:00:00.0000000 18:00:00.0000000 ryan 01:00:00.0000000 06:00:00.0000000 <pre>
The result is returning correctly as there is only on record between 12:00 to 17:00. If you want if the clinic is open between 12:00 to 17:00 then try this query. Replace @a with your table name.
Select CONVERT(Time,'12:00'),CONVERT(Time,'17:00'),ClinicName , FromTime ,ToTime from @a WHERE (1=1) and (( FromTime <= CONVERT(Time,'12:00') and ToTime >= CONVERT(Time,'12:00')) or (FromTime <= CONVERT(Time,'17:00') and ToTime >= CONVERT(Time,'17:00') )
这篇关于从数据库中过滤时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!