请教sql字符串日期查询

[xiehuangda]

表名：Biao

id,shi,aName
1 20140205090312 红茶
2 20151211132401 绿茶
3 20160103050406 可乐
4 20160204060809 雪碧

时间在表里设计的是字符串型的，格式如上。
怎么样，根据日期范围查询出数据来呢？例如用between来查询 2015-9-9到2016-1-20的数据，请教怎么写呢？

[路过路人乙]

select  * from Biao where shi between'20150909000000' and '20160120235959'

传进去的参数自己先做一下处理即可.

[中国风]

use Tempdb
go
--> --> 
 
declare @Biao table([id] int,[shi] VARCHAR(20),[aName] nvarchar(22))
Insert @Biao
select 1,20140205090312,N'红茶' union all
select 2,20151211132401,N'绿茶' union all
select 3,20160103050406,N'可乐' union all
select 4,20160204060809,N'雪碧'


--参数
DECLARE @Start DATETIME,@End DATETIME
SELECT @Start='2015-9-9',@End='2016-1-20'

--处理参数格式

Select * from @Biao WHERE [shi] BETWEEN CONVERT(VARCHAR(8),@Start,112)+'000000'  AND CONVERT(VARCHAR(8),@End,112)+'235959'
/*
id	shi	aName
2	20151211132401	绿茶
3	20160103050406	可乐
*/

[西阳楼望]

select	biao.id
		,shi
		,Biao.aName
from	(
	select 1 as id, '20140205090312' as shi,N'红茶' as aName
	union
	select 2 as id, '20151211132401' as shi,N'绿茶' as aName
	union
	select 3 as id, '20160103050406' as shi,N'可乐' as aName
	union
	select 4 as id, '20160204060809' as shi,N'雪碧' as aName
) as Biao
where	CONVERT(datetime,LEFT(biao.shi,8),105) between '2015-9-9' and '2016-1-20'

[dongsheng10101]

引用 1 楼 HelpToMe 的回复:

select  * from Biao where shi between'20150909000000' and '20160120235959'

传进去的参数自己先做一下处理即可.

同上， 这种方式处理速度最快