初学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 件処理されました)

回复赞

昵称被占用了 2008-04-07

本周
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
是否有不同值.即开始,结束都是一个时间吗?如果不是,就麻烦了.

回复赞

昵称被占用了 2008-04-07

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

回复赞

昵称被占用了 2008-04-07

2
convert(varchar(16),plan_addtime,120) as addtime

回复赞