Find a sql statement?

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


My field name is ntime type is varchar and I want to find two statements
One is to select all 2016 (from 2016-12-14 08:26 to 2016-12-30 09:48) and the other is to select all 2017 (from 2017-01-04 09:09 to 2017- 01-05 09:18) younger brother, beginners, etc.

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

Is the above data a record?

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