如何让所有时间都超过SQL中的特定时间 [英] How to get all times greater than a particular time in SQL
问题描述
大家好,
我有一个字段表,即开始时间。我需要的是,我需要获得比特定时间更长的所有开始时间。我把时间存储在varchar类型中。
我尝试过:
例如,
Hi all,
I have a table with a field ie starttimes . What I need is that , I need to get all the starttimes that is greater than a particular time . I have stored the time in varchar type.
What I have tried:
For example ,
StartTimes
1:00 AM
2:00 AM
3:00 AM
4:00 AM
7:00 AM
1:00 PM
2:00 PM
3:00 PM
等等。
如果我选择上午7:00,我需要的时间大于上午7:00,即所有时间段都在12小时内,即从早上7点开始到晚上11:59。怎么做。
任何帮助都会非常感激。谢谢你。
and so on .
If I select 7:00 AM , I need to get all the times greater than 7:00 AM ie all times in time field within 12 Hrs ie from 7:00 AM TO 11:59 PM.How to do this.
Any help will be really appreciated .Thanks in Advance.
推荐答案
使用这个样本data
Using this sample data
create table #badway
(
id integer identity(1,1),
mytime varchar(8)
)
insert into #badway (mytime) values
('1:00 AM'),
('2:00 AM'),
('3:00 AM'),
('4:00 AM'),
('7:00 AM'),
('1:00 PM'),
('2:00 PM'),
('3:00 PM')
然后此查询符合您的标准
Then this query fits your criteria
select id, convert(time, mytime) from #badway
where convert(time, mytime) between '07:00:00' and '23:59:00'
请注意使用24小时制。
正如我在评论中所说,您应该使用时间
列类型来存储时间,而不是 varchar
,然后就不需要转换
Note the use of the 24hr clock.
As I said in my comment, you should use time
column types to store times, not varchar
, then there would be no need for the convert
使用SQL Server DATEPART()函数 [ ^ ]。
Use SQL Server DATEPART() Function[^].
SELECT * FROM [your table] WHERE DATEPART(hh, [your date field]) = 7 --7 AM
KR
KR
这篇关于如何让所有时间都超过SQL中的特定时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!