Ask time to deal with the problem

zhuxuewei110

Table tb1 structure:
    f1 int autoincrement
    f2 datetime

The value of f2 is roughly as follows:
2016-12-20 22: 22: 32.000
2016-12-20 23: 32: 38.000
2016-12-20 00: 24: 49.000

declare t1 char (5),
        t2 char (5),
        t3 datetime

set t1 = '23: 30 '
set t1 = '00: 30 '

Now how to find out the record of the time part of the field f2 in the range t1 to t2 in the table tb1

我是杀人狂

select * from tablename where f2 between cast (('2016-12-20' + '23: 30 '+': 000 ') as datetime) and cast ((' 2016-12-21 '+ '00: 30' + ': 000') as datetime)

我是杀人狂

declare @a table (id int identity (1,1), f2 datetime)
insert into @a select '2016-12-20 22: 22: 32.000' union all
select '2016-12-20 23: 32: 38.000' union all
select '2016-12-20 00: 24: 49.000'
select * from @a where f2 between
cast (('2016-12-20' + '23: 30 '+': 000 ') as datetime)
and cast (('2016-12-21' + '00: 30 '+': 000 ') as datetime)
result:
id f2
----------- --------------------------------------- ---------------
2 2016-12-20 23: 32: 38.000

(1 row affected)

sinexe

select * from tablename
where convert (varchar (10), f2,108) between '23: 30 'and '00: 30'

fanfan1980

create table T (f1 int, f2 datetime)
insert T select 1, '2016-12-20 22: 22: 32.000'
union all select 2, '2016-12-20 23: 32: 38.000'
union all select 3, '2016-12-20 00: 24: 49.000'

declare @ t1 char (5), @ t2 char (5)
set @ t1 = '23: 30 '
set @ t2 = '00: 30 '

select * from T
where f2 between (convert (char (10), f2, 120) + '' + @ t1) and (convert (char (10), f2 + 1, 120) + '' + @ t2)
--result
f1 f2
----------- --------------------------------------- ---------------
2 2016-12-20 23: 32: 38.000

(1 row (s) affected)

zhuxuewei110

fanfan1980:
Your sentence looks for the record of '2016-12-20 00: 24: 49.000'

zhuxuewei110

fanfan1980:
Your sentence could not find the record for '2016-12-20 00: 24: 49.000'

zhuxuewei110

Many time records:
2016-12-21 23: 47: 10.000
2016-12-21 23: 54: 19.000
2016-12-27 23: 55: 25.000
2016-12-23 23: 54: 14.000
2016-12-24 00: 04: 14.000
2016-12-24 23: 58: 42.000
.
.
.
.
.
.

12468866

create table #t (f1 int, f2 datetime)
insert #t select 1, '2016-12-20 22: 22: 32.000'
union all select 2, '2016-12-20 23: 32: 38.000'
union all select 3, '2016-12-20 00: 24: 49.000'

select * from #t where datepart (hh, dateadd (mi, 30, f2)) between 0 and 1

zhuxuewei110

Still not good, you can also find out 2016/12/28 00:34:11