用户输入 2011-1
能得到如下数据
1-1~1-7 第一个星期
1-8~1-14 第二个星期
1-15~1-21 第三个星期
1-22~1-28 第四个星期
29-31 第五个星期
能得到如下数据
1-1~1-7 第一个星期
1-8~1-14 第二个星期
1-15~1-21 第三个星期
1-22~1-28 第四个星期
29-31 第五个星期
解决方案 »
- 使用asp.net mvc, jquery, jquery ui构建基于ajax的ria应用
- onclick中 前后加\是什么意思?
- 奉100分以谢高手,表达敬意 Assembly
- html文件如何传参数到asp文件
- updatepanel中能使用this.response.write()吗!?
- datagrid如何动态在一个列里面添加2个按钮,要能点击事件,目前实现添加按钮但无法相应事件
- 为什么我的电脑用microsoft.jet.oledb.4.0打不开2000access的?
- web.Config一這樣設置,WebService就出錯?誰知道,高手請指點,謝!
- asp.net 问题
- 高手们:如何实现datatable中的数据转换成excel?先答对者先得分。。。。
- CSDN右侧弹出小窗口是怎么做的??
- 通过客户端的HTTP请求,查询请求中是否有要求回发的控件,获得控件ID
先算出这个月的第一天是星期几
DateTime t = new DateTime(2010, 1, 1);
string str = t.DayOfWeek.ToString();
之后以一周7天一个分段,自己进行分割就行了
declare @month as varchar(7)
set @month = '2012-12'
select 日,一,二,三,四,五,六 from
(
select week ,
max(case weekday when 1 then datename(day,dt) else '' end ) '日',
max(case weekday when 2 then datename(day,dt) else '' end ) '一',
max(case weekday when 3 then datename(day,dt) else '' end ) '二',
max(case weekday when 4 then datename(day,dt) else '' end ) '三',
max(case weekday when 5 then datename(day,dt) else '' end ) '四',
max(case weekday when 6 then datename(day,dt) else '' end ) '五',
max(case weekday when 7 then datename(day,dt) else '' end ) '六'
from
(
select week = datepart(week , m.dt) , weekday = datepart(weekday , m.dt) , dt from
(
select dt = @month + '-' + right('00'+cast(t.id as varchar),2) from
(
select 1 as id union select 2 union select 3 union select 4 union select 5
union select 6 union select 7 union select 8 union select 9 union select 10
union select 11 union select 12 union select 13 union select 14 union select 15
union select 16 union select 17 union select 18 union select 19 union select 20
union select 21 union select 22 union select 23 union select 24 union select 25
union select 26 union select 27 union select 28 union select 29 union select 30
union select 31
) t
where isdate(@month + '-' + right('00'+cast(t.id as varchar),2)) = 1 and @month + '-' + right('00'+cast(t.id as varchar),2) <= dateadd(month , 1 , @month + '-01')
) m
) n
group by week
) o
set @first='2001-10'
declare @idTable table(id int, date datetime);select @daycount=datepart(day, dateadd(day,-1,dateadd(mm, 1, convert(datetime, @first+'-1'))));with temp
as
(
select top 31 row_number() over(order by id) as id from syscolumns
)insert into @idTable
select id, convert(datetime, @first+'-'+convert(nvarchar(2), id))
from temp
where id <= @daycountselect 起始日期=convert(nvarchar(10),min(date),120)+'~'+convert(nvarchar(10),max(date),120)
,第几周=case
when (datepart(dd,date)-1) / 7 = 0 then '第一周'
when (datepart(dd,date)-1) / 7 = 1 then '第二周'
when (datepart(dd,date)-1) / 7 = 2 then '第三周'
when (datepart(dd,date)-1) / 7 = 3 then '第四周'
when (datepart(dd,date)-1) / 7 = 4 then '第五周'
end
from @idTable
group by (datepart(dd,date)-1) / 7
go
create proc getWeeks @input varchar(20)
as
declare @now datetime
declare @month varchar(2)
declare @days int
declare @tb table(dayRang varchar(100),weekIndex varchar(100))
begin
set @input = @input + '-1'
set @now = convert(datetime,@input)
set @month = datepart(month,@now)
set @days = datediff(day,@now,dateadd(month,1,@now))
declare @i int
set @i = 1
declare @str1 varchar(20)
declare @str2 varchar(40)
declare @temp int
declare @j int
set @j = 1
while @i <= @days
begin
if @i + 6 <= @days
begin
set @temp = @i + 6
end
else
begin
set @temp = @days
end
set @str1 = @month + '-' + convert(varchar,@i) + '~' + @month + '-' + convert(varchar,@temp)
if @j = 1
begin
set @str2 = '一'
end
else if @j = 2
begin
set @str2 = '二'
end
else if @j = 3
begin
set @str2 = '三'
end
else if @j = 4
begin
set @str2 = '四'
end
else
begin
set @str2 = '五'
end
set @str2 = '第' + @str2 + '个星期'
insert into @tb select @str1,@str2
set @i = @i + 7
set @j = @j + 1
endselect * from @tb
end
go
exec getWeeks '2011-1'