select * from table where year(dt)>='2009' and year(dt)<='2010' and left(replace(convert(char(10),dt,108),':',''),4)+'00'>='0330' and left(replace(convert(char(10),dt,108),':',''),4)+'00'<='0820'
select * from table where year(dt)>='2009' and year(dt)<='2010' and left(replace(convert(char(10),dt,108),':',''),4)+'00'>='033000' and left(replace(convert(char(10),dt,108),':',''),4)+'00'<='082000'
--2009年到2010年 3点30分到8点20分 --> 测试数据: #ta if object_id('tempdb.dbo.#ta') is not null drop table #ta go create table #ta (date datetime) insert into #ta select '2009-10-01 03:05:00' union all select '2009-10-01 03:35:00' union all select '2010-11-01 06:05:00' union all select '2010-10-01 09:05:00'select * from #ta where year(date)in(2009,2010) and CONVERT(varchar(8) , date, 108) between '03:30:00' and '08:20:00'date ----------------------- 2009-10-01 03:35:00.000 2010-11-01 06:05:00.000(2 行受影响)
Select * From T1 where Year(Time1) between 2009 and 2010 and CONVERT(varchar(100), Time1,8) between '03:30:00' and '08:20:00'
2009年到2010年 3点30分到8点20分 的数据? 没有具体日期? select * from 表名 where 时间 between '2009-1-1 03:30:00' and '2010-1-1 08:20:00'?
select * from table where convet(char(8),dt,112)>='20090110' and convet(char(8),dt,112)<='20100210' and left(replace(convert(char(10),dt,108),':',''),4)+'00'>='033000' and left(replace(convert(char(10),dt,108),':',''),4)+'00'<='082000'
Select * From T1 where CONVERT(varchar(100), Time1, 111): between '2009/01/10' and '2010/02/10' and CONVERT(varchar(100), Time1,8)Time1, 20) between '03:30:00' and '08:20:00'
Select * From T1 where CONVERT(varchar(100), Time1, 111) between '2009/01/10' and '2010/02/10' and CONVERT(varchar(100), Time1,8)Time1, 20) between '03:30:00' and '08:20:00'
SELECT * FROM tb WHERE CONVERT(CHAR(10),[datetime] ,120) BETWEEN '2009-01-10' AND '2010-02-10' AND CONVERT(CHAR(8),[datetime],108) BETWEEN '03:30:00' AND '08:20:00'
if object_id('tempdb.dbo.#ta') is not null drop table #ta
go
create table #ta (date datetime)
insert into #ta
select '2009-10-01 03:05:00' union all
select '2009-10-01 03:35:00' union all
select '2010-11-01 06:05:00' union all
select '2010-10-01 09:05:00'select * from #ta
where year(date)in(2009,2010)
and CONVERT(varchar(8) , date, 108) between '03:30:00' and '08:20:00'date
-----------------------
2009-10-01 03:35:00.000
2010-11-01 06:05:00.000(2 行受影响)
没有具体日期?
select * from 表名 where 时间 between '2009-1-1 03:30:00' and '2010-1-1 08:20:00'?
Select * From T1 where CONVERT(varchar(100), Time1, 111): between '2009/01/10' and '2010/02/10' and
CONVERT(varchar(100), Time1,8)Time1, 20) between '03:30:00' and '08:20:00'
CONVERT(varchar(100), Time1,8)Time1, 20) between '03:30:00' and '08:20:00'
AND
CONVERT(CHAR(8),[datetime],108) BETWEEN '03:30:00' AND '08:20:00'