Create Table #test(D1 varchar(50),D2 varchar(50))
Insert Into #test(D1,D2) Values('2006-12-1 00:00:00','2006-12-3 00:00:00')
Insert Into #test(D1,D2) Values('2006-12-3 00:00:00','2006-12-5 00:00:00')
Insert Into #test(D1,D2) Values('2006-12-5 00:00:00','2006-12-7 00:00:00')
Insert Into #test(D1,D2) Values('2006-12-13 00:00:00','2006-12-15 00:00:00')Select * from #test where (d1<='2006-12-13 16:39:00'and d2>='2006-12-13 16:39:00')Drop table #testvarchar 改成 Datetime 问题就解决。教训阿!(好像表不是我设计的)
Insert Into #test(D1,D2) Values('2006-12-1 00:00:00','2006-12-3 00:00:00')
Insert Into #test(D1,D2) Values('2006-12-3 00:00:00','2006-12-5 00:00:00')
Insert Into #test(D1,D2) Values('2006-12-5 00:00:00','2006-12-7 00:00:00')
Insert Into #test(D1,D2) Values('2006-12-13 00:00:00','2006-12-15 00:00:00')Select * from #test where (d1<='2006-12-13 16:39:00'and d2>='2006-12-13 16:39:00')Drop table #testvarchar 改成 Datetime 问题就解决。教训阿!(好像表不是我设计的)
2006-12-1 00:00:00
2006-12-01 00:00:00
对于Datetime来说上面两个是一样的,但对于varchar来说差别很大了,所以能用Datetime还是不要用varchar了,再者说Datetime无论从检索速度还是占用空间来说,都有很大优势
如果想用VARCHAR存日期, 得保证格式固定,每个域的长度一致才行啊!