求几条语句 数据库字段 id name time
20 123 2012-8-10 2:11:40我想查询2012-8-10 到2012-8-20之间的记录数。。 这是一条语句。。然后列出2012-8-10到2012-8-20之间的记录 这是第二条语句。。在一条就是想查询2012-8-10的记录数 新手不懂。。 求教
20 123 2012-8-10 2:11:40我想查询2012-8-10 到2012-8-20之间的记录数。。 这是一条语句。。然后列出2012-8-10到2012-8-20之间的记录 这是第二条语句。。在一条就是想查询2012-8-10的记录数 新手不懂。。 求教
select count(*) from tablename where time >='2012-08-10' and time <='2012-08-20'select * from tablename where time >='2012-08-10' and time <='2012-08-20'select count(*) from tablename where time >='2012-08-10' and time <'2012-08-11'
1、select * from tb where time>='2012-08-10 00:00:00' and time<='2012-08-20 00:00:00'2、DECLARE @time DATETIME
DECLARE @time2 DATETIME
SET @time='2012-08-10'
SET @time2='2012-08-20'
select
dateadd(dd,num,@time)
from
(select isnull((select count(1) from sysobjects where id <t.id),0) as num from sysobjects t) a
where
dateadd(dd,num,@time) <=@time2
/*
2012-08-10 00:00:00.000
2012-08-11 00:00:00.000
2012-08-12 00:00:00.000
2012-08-13 00:00:00.000
2012-08-14 00:00:00.000
2012-08-15 00:00:00.000
2012-08-16 00:00:00.000
2012-08-17 00:00:00.000
2012-08-18 00:00:00.000
2012-08-19 00:00:00.000
2012-08-20 00:00:00.000
*/3、select * from tb where time>='2012-08-10 00:00:00' and time<='2012-08-11 00:00:00'
--2012-08-10到2012-08-20之间的日期
DECLARE @begin_date DATETIME
DECLARE @end_date DATETIME
SET @begin_date ='2012-08-10'
SET @end_date='2012-08-20'
SELECT DATEADD(dd,number,@begin_date)
FROM master..spt_values WHERE type='p' AND number BETWEEN 0 AND DATEDIFF(dd,@begin_date,@end_date) --2012-08-10记录数
SELECT COUNT(*) FROM [你的表]
WHERE CONVERT(VARCHAR(10),[time],120)='2012-08-10'
如果说我要查询当天的数据 这样写是否正确 select count(*) from tablename where time =getdate()
declare @endtime datetime
set @begintime='2012-8-10'
set @endtime='2012-8-20';with f as
(
select @begintime as dt
union all
select dateadd(dd,1,dt) from f where dateadd(dd,1,dt)<=@endtime
)
select dt from f option(maxrecursion 0)/*dt
-----------------------
2012-08-10 00:00:00.000
2012-08-11 00:00:00.000
2012-08-12 00:00:00.000
2012-08-13 00:00:00.000
2012-08-14 00:00:00.000
2012-08-15 00:00:00.000
2012-08-16 00:00:00.000
2012-08-17 00:00:00.000
2012-08-18 00:00:00.000
2012-08-19 00:00:00.000
2012-08-20 00:00:00.000(11 行受影响)*/
2、DECLARE @time DATETIME
DECLARE @time2 DATETIME
SET @time='2012-08-10'
SET @time2='2012-08-20'
select convert(varchar(10),dateadd(day,number,@time),120)
from
master..spt_values
where
datediff(day,dateadd(day,number,@time), @time2)>=0
and number>0
and type='p'
/*
2012-08-11
2012-08-12
2012-08-13
2012-08-14
2012-08-15
2012-08-16
2012-08-17
2012-08-18
2012-08-19
2012-08-20*/