我有一个字段类型是datetime(8) 。我现在想统计一段时间内的营业额。这个字段的内容也给大家贴出来,eg: 2007-04-06 00:00:00.000select * from t_counter_out_all where [date] between 什么?你看我想统计04-06 到04-07 怎么写?
select * from t_counter_out_all where convert(varchar(100),[date],120) between '2007-04-06' and '2007-04-07'
select * from t_counter_out_all where convert(varchar(100),[date],120) between '2007-04-06 00:00:00.000' and '2007-04-07 00:00:00.000' 两段的sql 的结果竟然一个是06 的数据,一个是07 的数据。诡异!
select * from t_counter_out_all where [date] between '2007-04-06' and '2007-04-07'
select * from t_counter_out_all where convert(varchar(100),[date],120) between '2007-04-06 00:00:00.000' and '2007-04-08 00:00:00.000'
declare @t table(id int,[date] datetime) insert @t select 1, '2009-4-04' union all select 2, '2009-4-06' union all select 3, '2009-4-07' union all select 4, '2009-4-08' union all select 5, '2009-5-09' union all select 6, '2009-4-07'select * from @t where convert(varchar(10),[date],120)--问题在这convert(varchar(100),[date],120) between '2009-04-06' and '2009-04-07'id date ----------- ----------------------- 2 2009-04-06 00:00:00.000 3 2009-04-07 00:00:00.000 6 2009-04-07 00:00:00.000(3 行受影响)
你这样转成字符再来比较肯定有问题 SELECT * FROM t_counter_out_all WHERE DATEDIFF(DD,'2007-04-06',[DATE])=0 OR DATEDIFF(DD,'2007-04-07',[DATE])=0
select * from @t where [date] between '2009-01-06' and '2009-04-07' --另不要对[date]转换id date ----------- ----------------------- 2 2009-04-06 00:00:00.000 3 2009-04-07 00:00:00.000 6 2009-04-07 00:00:00.000(3 行受影响)
7楼,代码粘错: declare @t table(id int,[date] datetime) insert @t select 1, '2009-4-04' union all select 2, '2009-4-06' union all select 3, '2009-4-07' union all select 4, '2009-4-08' union all select 5, '2009-5-09' union all select 6, '2009-4-07'select * from @t where [date] between '2009-04-06' and '2009-04-07' --另不要对[date]转换id date ----------- ----------------------- 2 2009-04-06 00:00:00.000 3 2009-04-07 00:00:00.000 6 2009-04-07 00:00:00.000(3 行受影响)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ select * from t_counter_out_all where convert( varchar( 08 ) ,date , 112 ) between '20070406' and '20070407'or select * from t_counter_out_all where date >= '2007-4=6' and date <= '2007-4-7'
select * from t_counter_out_all where convert(varchar(100),[date],120)
between '2007-04-06' and '2007-04-07'
select * from t_counter_out_all where convert(varchar(100),[date],120)
between '2007-04-06 00:00:00.000' and '2007-04-07 00:00:00.000'
两段的sql 的结果竟然一个是06 的数据,一个是07 的数据。诡异!
between '2007-04-06' and '2007-04-07'
between '2007-04-06 00:00:00.000' and '2007-04-08 00:00:00.000'
insert @t
select 1, '2009-4-04' union all
select 2, '2009-4-06' union all
select 3, '2009-4-07' union all
select 4, '2009-4-08' union all
select 5, '2009-5-09' union all
select 6, '2009-4-07'select * from @t
where convert(varchar(10),[date],120)--问题在这convert(varchar(100),[date],120)
between '2009-04-06' and '2009-04-07'id date
----------- -----------------------
2 2009-04-06 00:00:00.000
3 2009-04-07 00:00:00.000
6 2009-04-07 00:00:00.000(3 行受影响)
SELECT * FROM t_counter_out_all WHERE
DATEDIFF(DD,'2007-04-06',[DATE])=0 OR DATEDIFF(DD,'2007-04-07',[DATE])=0
select * from @t
where [date] between '2009-01-06' and '2009-04-07'
--另不要对[date]转换id date
----------- -----------------------
2 2009-04-06 00:00:00.000
3 2009-04-07 00:00:00.000
6 2009-04-07 00:00:00.000(3 行受影响)
declare @t table(id int,[date] datetime)
insert @t
select 1, '2009-4-04' union all
select 2, '2009-4-06' union all
select 3, '2009-4-07' union all
select 4, '2009-4-08' union all
select 5, '2009-5-09' union all
select 6, '2009-4-07'select * from @t
where [date] between '2009-04-06' and '2009-04-07'
--另不要对[date]转换id date
----------- -----------------------
2 2009-04-06 00:00:00.000
3 2009-04-07 00:00:00.000
6 2009-04-07 00:00:00.000(3 行受影响)
select *
from t_counter_out_all
where convert( varchar( 08 ) ,date , 112 ) between '20070406' and '20070407'or
select *
from t_counter_out_all
where date >= '2007-4=6'
and date <= '2007-4-7'