表(Table)结构是
id 点击次数 timetime 是包含年月日时分秒的select count(*) from Table where time>时间1 and time<时间2 group by day(time)“day(time)”这个是求出“天”的,比如day(2009-9-23 12:01:01)=23
sqlserver里有什么函数可以求出得到"2009-9-23" 呢, 上面的sqlserver语句怎么写呢,好像是没有date(2009-9-23 12:01:01)这种写法的,我的group by 要按照"2009-9-23"这样的格式来,该怎么弄,请高手帮忙看看。谢谢了。
id 点击次数 timetime 是包含年月日时分秒的select count(*) from Table where time>时间1 and time<时间2 group by day(time)“day(time)”这个是求出“天”的,比如day(2009-9-23 12:01:01)=23
sqlserver里有什么函数可以求出得到"2009-9-23" 呢, 上面的sqlserver语句怎么写呢,好像是没有date(2009-9-23 12:01:01)这种写法的,我的group by 要按照"2009-9-23"这样的格式来,该怎么弄,请高手帮忙看看。谢谢了。
declare @s datetime
set @s='2004-8-30 04:13:54'
select convert(char(10),@s,120),convert(char(8),@s,114)
/*
---------- --------
2004-08-30 04:13:54
convert(varchar(10),[time],120) as [date],
count(*)
from
[Table]
where
[time]>时间1 and [time] <时间2
group by
convert(varchar(10),[time],120)
convert(varchar(10),[time],120) as [date],
count(*)
from
[Table]
where
[time]>时间1 and [time] <时间2
group by
convert(varchar(10),[time],120)
请各位再看看哦,谢谢哦。
回公司再测试测试。
drop table tb
go
create table tb(id datetime)
insert into tb
select '2009-2-23 08:10' union all
select '2009-2-23 08:10' union all
select '2009-2-23 08:10' union all
select '2009-2-23 08:10' union all
select '2009-2-23 08:10' union all
select '2009-2-23 08:10' union all
select '2009-2-23 08:10'
select count(*) from tb where id>'2009-2-22' and id<'2009-2-24' group by
(cast((datepart(year,id)+'-'+datepart(month,id)+'-'+datepart(day,id) ) as datetime))
结果为7
select count(*) from Table where time>时间1 and time <时间2 group by year(time),month(time),day(time)
convert(varchar(10),[time],120) as [date],
count(*)
from
[Table]
where
[time]>时间1 and [time] <时间2
group by
convert(varchar(10),[time],120)