select * from [表] where orderdate>='2012-06-01 00:00:00' and orderdate<='2012-06-30 23:59:59'
和
select * from [表] where orderdate>='2012-06-01' and orderdate<='2012-06-30'
查询的记录是一样的吗?
如果不一样,为什么不一样?
和
select * from [表] where orderdate>='2012-06-01' and orderdate<='2012-06-30'
查询的记录是一样的吗?
如果不一样,为什么不一样?
select CAST('2012-06-01' as datetime)
/*
2012-06-01 00:00:00.000
*/
select CAST('2012-06-30' as datetime)
/*
2012-06-30 00:00:00.000
*/--日期后面没有的部分都是默人用0补上。说以你说的那两个是一样的
insert tb select '2012-06-01' union all select '2012-06-01 00:35:15.001'
union all select '2012-06-02' union all select '2012-06-02 10:35:15.001'
union all select '2012-06-12' union all select '2012-06-12 10:35:15.001'
union all select '2012-06-22' union all select '2012-06-22 23:59:59.001'
union all select '2012-06-30' union all select '2012-06-30 23:59:59.001'
select * from tb where orderdate>='2012-06-01 00:00:00' and orderdate<='2012-06-30 23:59:59'
/*
id orderdate
1 2012-06-01 00:00:00.000
2 2012-06-01 00:35:15.000
3 2012-06-02 00:00:00.000
4 2012-06-02 10:35:15.000
5 2012-06-12 00:00:00.000
6 2012-06-12 10:35:15.000
7 2012-06-22 00:00:00.000
8 2012-06-22 23:59:59.000
9 2012-06-30 00:00:00.000
10 2012-06-30 23:59:59.000
*/
select * from tb where orderdate>='2012-06-01' and orderdate<='2012-06-30'
/*
1 2012-06-01 00:00:00.000
2 2012-06-01 00:35:15.000
3 2012-06-02 00:00:00.000
4 2012-06-02 10:35:15.000
5 2012-06-12 00:00:00.000
6 2012-06-12 10:35:15.000
7 2012-06-22 00:00:00.000
8 2012-06-22 23:59:59.000
9 2012-06-30 00:00:00.000
*/
第二个查询缺少 10 2012-06-30 23:59:59.000实践出真知drop table tb
--orderdate 不一样。'2012-06-30 23:59:59' 和 '2012-06-30'(即'2012-06-30 00:00:00') 有毫秒上的差别。
--例如 : 2012-06-30 23:59:59.003 至 2012-06-30 23:59:59.997 前一个SQL就查询不出来了