--早说吗 白拿你分了。
declare @d table (序号 int,日期 datetime,星期 varchar(10) )
declare @x datetime
declare @cnt datetime
set @x='2003-01-01'
set @cnt=dateadd(year,1,@x)
while @cnt>@x
begin
if datepart(weekday,@x) in (1,7)
insert into @d (序号 ,日期,星期 )
select convert(int,@x-dateadd(year,-1,@cnt)) as 序号,@x as 日期 ,
case when datepart(weekday,@x)=1 then '星期天' else '星期六' end as 星期
set @x=@x+1
end
select * from @d
--无聊 再来个改进
declare @x datetime
declare @cnt datetime
set @x='2003-01-01'
set @cnt=dateadd(year,1,@x)
while @cnt>@x
begin
if datepart(weekday,@x) in (1,7)
select convert(int,@x-dateadd(year,-1,@cnt)) as 序号,@x as 日期 ,
case when datepart(weekday,@x)=1 then '星期天' else '星期六' end as 星期
set @x=@x+1
end
--可惜不知道access怎么做
Sub s_dtlist(ByVal dt1 As Date, ByVal dt2 As Date)
Dim dt As Date
For dt = dt1 To dt2
If DatePart("w", dt, vbMonday) = 6 Or DatePart("w", dt, vbMonday) = 7 Then
Debug.Print dt & vbTab & "星期" & Mid("六日", DatePart("w", dt, vbMonday) - 5, 1)
End If
Next
End Sub
--这个函数就是啦
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getdt]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_getdt]
GO
create function f_getdt(
@year int --要查询的年份
)returns @re table(id int identity(1,1),日期 datetime,星期 varchar(10))
as
begin
declare @tb table(id int identity(0,1),日期 datetime)
insert into @tb(日期) select top 366 null from sysobjects a ,sysobjects b
update @tb set 日期=dateadd(day,id,dateadd(year,@year-1900,'1900-1-1'))
insert into @re(日期,星期)
select 日期,datename(weekday,日期)
from @tb where datepart(weekday,日期) in (1,7)
return
end
go
--调用示例,得到2003年的.
select * from dbo.f_getdt(2003)
--得到2003年指定时间段的
select * from dbo.f_getdt(2003) where 日期 between '2003-01-01' and '2003-01-31'
declare @x datetime
declare @cnt datetime
set @x='2003-01-01'
set @cnt=dateadd(year,1,@x)
while @cnt>@x
begin
if datepart(weekday,@x) in (1,7)
select @x
set @x=@x+1
end
这是我的:
alter proc day_get
--获取每年的周末
@year varchar(10)
as
if object_id('tempdb..#tmp') is not null
drop table #tmp
create table #tmp(dat char(10))
declare @alldate varchar(20),@i int,@j int
select @alldate=@year+'0101'
select @j=datepart(dayofyear,@alldate)
select @i=datepart(weekday,@alldate)
while @j<datepart(dayofyear,dateadd(day,-1,cast((cast(@year as int)+1) as varchar(10))+'0101') )
begin
if @i in(1,7)
insert #tmp select convert(char(10),cast(@alldate as datetime),120)
select dateadd(day,id,@年) 时间,DATENAME(weekday,dateadd(day,id,@年)) 星期 from (
select top 366 (select sum(1) from sysobjects where id<=a.id) id from master..sysobjects a) tem
where datediff(year,@年,dateadd(day,id,@年))=0 and
(datepart(weekday,dateadd(day,id,@年)) in (1,7) or dateadd(day,id,@年) in
select dateadd(day,id,@年) from (
select top 366 (select sum(1) from sysobjects where id<=a.id) id from master..sysobjects a) tem
where datediff(year,@年,dateadd(day,id,@年))=0 and
(datepart(weekday,dateadd(day,id,@年)) in (1,7) or dateadd(day,id,@年) in
create function f_getdt(
@year int --要查询的年份
)returns @re table(id int identity(1,1),日期 datetime,星期 varchar(10))
as
begin
declare @tb table(id int identity(0,1),日期 datetime)
insert into @tb(日期) select top 366 null from sysobjects a ,sysobjects b
update @tb set 日期=dateadd(day,id,dateadd(year,@year-1900,'1900-1-1'))
insert into @re(日期,星期)
select 日期,datename(weekday,日期)
from @tb where datepart(weekday,日期) in (1,7)
return
end
go