求一sql语句？

chyhero

2016-12-14 08:26 |
| 2016-12-15 10:19 |
| 2016-12-20 08:50 |
| 2016-12-20 09:21 |
| 2016-12-20 10:13 |
| 2016-12-20 10:51 |
| 2016-12-21 14:15 |
| 2016-12-21 15:59 |
| 2016-12-25 09:37 |
| 2016-12-26 12:09 |
| 2016-12-27 11:44 |
| 2016-12-30 09:22 |
| 2016-12-30 09:48 |
| 2017-01-04 09:09 |
| 2017-01-04 11:00 |
| 2017-01-05 09:18


我的字段名是ntime 类型是varchar，想求两条语句
一个是把2016的都选出来（从2016-12-14 08:26到2016-12-30 09:48），另一个把2017的都选出来（从2017-01-04 09:09到2017-01-05 09:18）  小弟初学，在线等

prajna

select * from t where left(ntime,4)='2016'

我是杀人狂

select * from tablename where substring(ltrim(replace(ntime,'|','')),1,4)=2016
select * from tablename where substring(ltrim(replace(ntime,'|','')),1,4)=2017

cv4bf5d65fd

select * from tablename where ntime between '2016-12-14 08:26' and '2016-12-30 09:48'
select * from tablename where ntime between '2017-01-04 09:09' and '2017-01-05 09:18'

我是杀人狂

select * from tablename where substring(ltrim(replace(ntime,'|','')),1,4)='2016'
select * from tablename where substring(ltrim(replace(ntime,'|','')),1,4)='2017'

foxflea

select * from tablename where ColumnName between '2016-12-14 08:26' And '2016-12-30 09:48' Or ColumnName Between '2017-01-04 09:09' And '2017-01-05 09:18'

fanfan1980

以上的數據是一條記錄嗎？

fanfan1980

create table T(ntime varchar(1000))
insert T select
'2016-12-14 08:26 |
| 2016-12-15 10:19 |
| 2016-12-20 08:50 |
| 2016-12-20 09:21 |
| 2016-12-20 10:13 |
| 2016-12-20 10:51 |
| 2016-12-21 14:15 |
| 2016-12-21 15:59 |
| 2016-12-25 09:37 |
| 2016-12-26 12:09 |
| 2016-12-27 11:44 |
| 2016-12-30 09:22 |
| 2016-12-30 09:48 |
| 2017-01-04 09:09 |
| 2017-01-04 11:00 |
| 2017-01-05 09:18 '



declare @sql varchar(8000)
select @sql='select * from (select ntime='''
select @sql=@sql+replace(replace(replace(ntime, char(13), ''), char(10), ''), ' || ', ''' union all select ''') from T
select @sql=@sql+''') a where year(ntime)=2016'
exec(@sql)

--result
ntime            
-----------------
2016-12-14 08:26
2016-12-15 10:19
2016-12-20 08:50
2016-12-20 09:21
2016-12-20 10:13
2016-12-20 10:51
2016-12-21 14:15
2016-12-21 15:59
2016-12-25 09:37
2016-12-26 12:09
2016-12-27 11:44
2016-12-30 09:22
2016-12-30 09:48