初学SQL.请教大吓们两个问题?

qidian2009 2008-04-07 10:00:38
1.如何取得今日,昨日,本周,上周,本月,上月,本年,去年的数据
数据表:plan
plan_id plan_begintime plan_endtime
1 2006-3-5 2008-5-5
如果选择"今日",因"今日"包含在2006-3-5和2008-5-5之间,所以所取得上面的数据.
依次类推:如何取得昨日,本周,上周,本月,上月,本年,去年在开始时间和结束时间之间内的值

2.
数据表plan
plan_ id plan_addtime
1 2007-05-05 15:30:00
我用convert(varchar,plan_addtime,120) as addtime只能得到2007-05-05 15:30:00 ,如何只得到2007-05-05 15:30

...全文
127 点赞 收藏 9
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
yimeng123 2008-04-07
重薪诚聘C++
http://topic.csdn.net/u/20080227/14/8bce0844-bd15-42f0-9cda-a343d5d6601b.html
回复
qidian2009 2008-04-07
谢谢各位朋友,海阔天空能不能麻烦补充完,我还晃知道上周,本月,下月,去年,本年怎么取.
回复
qidian2009 2008-04-07
开始时间和结束时间不同.
回复
dawugui 2008-04-07
--今日
select * from plan where datediff(dd,plan_begintime,getdate()) >= 0 or datediff(dd,getdate(),plan_endtime) <= 0
--昨日
select * from plan where datediff(dd,plan_begintime,getdate()) >= 1 or datediff(dd,getdate(),plan_endtime) <= 1

--其他参考如下(获取所有的日期,然后逐一判断):
ID 开始时刻 结束时刻
134217743 2007-01-22 23:00:06.000 2007-01-23 23:04:34.000
134217838 2007-01-23 22:52:20.000 2007-01-23 23:04:44.000
现在需要统计出,每天每个小时每类ID的在线人数,只要有在某个小时段内就算在线一次,如第二条记录134217838 2007-01-23 22:52:20.000 2007-01-23 23:04:44.000
则需统计出:
134217838 2007-01-23 22
134217838 2007-01-23 23
第一条则统计出:
134217743 2007-01-22 23
134217743 2007-01-23 0
134217743 2007-01-23 1



134217743 2007-01-23 22
134217743 2007-01-23 23
谢谢各位!帮忙解决问题,帮忙顶!


declare @a table(ID varchar(20),开始时刻 smalldatetime,结束时刻 smalldatetime)
insert @a select '134217743','2007-01-22 23:00:06.000','2007-01-23 23:04:34.000'
union all select '134217838','2007-01-23 22:52:20.000','2007-01-23 23:04:44.000'

select top 100 id=identity( int,0,1) into # from syscolumns a,syscolumns b

select a.id,
convert(varchar(10),dateadd(hour,b.id,开始时刻),120)日期,
datepart(hour,dateadd(hour,b.id,开始时刻)) 时间数
from @a a,# b
where datediff(hour,dateadd(hour,b.id,开始时刻),结束时刻)>=0
order by a.id,日期,时间数

drop table #


/*
id 日期 时间数
-------------------- ---------- -----------
134217743 2007-01-22 23
134217743 2007-01-23 0
134217743 2007-01-23 1
134217743 2007-01-23 2
134217743 2007-01-23 3
134217743 2007-01-23 4
134217743 2007-01-23 5
134217743 2007-01-23 6
134217743 2007-01-23 7
134217743 2007-01-23 8
134217743 2007-01-23 9
134217743 2007-01-23 10
134217743 2007-01-23 11
134217743 2007-01-23 12
134217743 2007-01-23 13
134217743 2007-01-23 14
134217743 2007-01-23 15
134217743 2007-01-23 16
134217743 2007-01-23 17
134217743 2007-01-23 18
134217743 2007-01-23 19
134217743 2007-01-23 20
134217743 2007-01-23 21
134217743 2007-01-23 22
134217743 2007-01-23 23
134217838 2007-01-23 22
134217838 2007-01-23 23

(所影响的行数为 27 行)

*/


回复
wynlc 2008-04-07
declare @plan_addtime datetime
set @plan_addtime='2007-05-05 15:30:00'
select @plan_addtime
select convert(varchar(16),@plan_addtime,120) as plan_addtime
------------------------------------------------------
2007-05-05 15:30:00.000

(1 件処理されました)

plan_addtime
----------------
2007-05-05 15:30

(1 件処理されました)
回复
本周
select * from plan where convert(varchar(10),dateadd(day,1-datepart(weekday,getdate()),getdate()),120) between plan_begintime and plan_endtime
or convert(varchar(10),dateadd(day,7-datepart(weekday,getdate()),getdate()),120) between plan_begintime and plan_endtime


其他类似
回复
dawugui 2008-04-07
2.
数据表plan
plan_ id plan_addtime
1 2007-05-05 15:30:00
我用convert(varchar,plan_addtime,120) as addtime只能得到2007-05-05 15:30:00 ,如何只得到2007-05-05 15:30

--------------------------
convert(varchar(16),plan_addtime,120)

---------------------------
1.如何取得今日,昨日,本周,上周,本月,上月,本年,去年的数据
数据表:plan
plan_id plan_begintime plan_endtime
1 2006-3-5 2008-5-5
如果选择"今日",因"今日"包含在2006-3-5和2008-5-5之间,所以所取得上面的数据.
依次类推:如何取得昨日,本周,上周,本月,上月,本年,去年在开始时间和结束时间之间内的值
-------------------------------
plan_begintime plan_endtime
是否有不同值.即开始,结束都是一个时间吗?如果不是,就麻烦了.

回复
1
今日
select * from plan where convert(varchar(10),getdate(),120) between plan_begintime and plan_endtime
昨日
select * from plan where convert(varchar(10),dateadd(day,-1,getdate()),120) between plan_begintime and plan_endtime
回复
2
convert(varchar(16),plan_addtime,120) as addtime
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-04-07 10:00
社区公告
暂无公告