create function kk ( @begintime datetime, @endtime datetime ) returns int as begin declare @n int set @N=0 set datefiRst 1 while(@begintime<=@endtime) begin if(select datepart(weekday,@begintime)=7) set @n=@n+1 set @begintime=dateadd(day,1,@begintime) end return @n end
CREATE FUNCTION F_WEEKDAY(@BT DATETIME,@ET DATETIME) RETURNS INT AS BEGIN DECLARE @DAYS INT,@I INT; SELECT @I= DATEDIFF(DAY,@BT,@ET)+1, @DAYS = @I/7*2, @BT=DATEADD(DAY,@I/7*7,@BT); RETURN @DAYS END GO select dbo.F_WEEKDAY('2009-08-01','2009-08-31')/*----------- 8(1 行受影响) */小梁已经写出来了,封装一下即可.
create function kk1 ( @begintime datetime, @endtime datetime ) returns int as begin declare @n int set @N=0 while(@begintime<=@endtime) begin if(select datepart(weekday,@begintime))=7 or(select datepart(weekday,@begintime))=6 set @n=@n+1 set @begintime=dateadd(day,1,@begintime) end return @n end set datefirst 1 DECLARE @begintime DATETIME,@endtime DATETIME; SELECT @begintime = '2009-08-01',@endtime = '2009-08-31'; select dbo.kk1(@begintime,@endtime) /* ----------- 10*/
--菜鸟水平,抛砖引玉 set datefiRst 1DECLARE @DATE DATETIME DECLARE @DAY INT SELECT @DATE='2009-08-07',@DAY=7 SELECT @DATE,datepart(weekday, @DATE) 'DATEPART',datename(weekday, @DATE) ,@DAY%7 '%' ,@DAY/7 '/' ,(@DAY/7)*2+CASE @DAY%7 WHEN 0 THEN 0 WHEN 1 THEN CASE WHEN datepart(weekday, @DATE)>5 THEN 1 ELSE 0 END WHEN 2 THEN CASE WHEN datepart(weekday, @DATE)>5 THEN 8-datepart(weekday, @DATE) ELSE 0 END WHEN 3 THEN CASE WHEN datepart(weekday, @DATE)>3 THEN CASE WHEN (datepart(weekday, @DATE)%3)%2>0 THEN 1 ELSE 2 END ELSE 0 END WHEN 4 THEN CASE WHEN datepart(weekday, @DATE)>2 THEN CASE WHEN (datepart(weekday, @DATE))%4<3 THEN 2 ELSE 1 END ELSE 0 END WHEN 5 THEN CASE WHEN datepart(weekday, @DATE)>1 THEN CASE WHEN (datepart(weekday, @DATE))IN (2,7) THEN 1 ELSE 2 END ELSE 0 END WHEN 6 THEN CASE WHEN (datepart(weekday, @DATE))IN (1,7) THEN 1 ELSE 2 END END
--正式版 set datefiRst 1DECLARE @DATE DATETIME,@DAY INT SELECT @DATE='2009-08-07',@DAY=7 SELECT @DATE '当前日期',datename(weekday, @DATE) '当前星期' ,(@DAY/7)*2+CASE @DAY%7 WHEN 0 THEN 0 WHEN 1 THEN CASE WHEN datepart(weekday, @DATE)>5 THEN 1 ELSE 0 END WHEN 2 THEN CASE WHEN datepart(weekday, @DATE)>5 THEN 8-datepart(weekday, @DATE) ELSE 0 END WHEN 3 THEN CASE WHEN datepart(weekday, @DATE)>3 THEN CASE WHEN (datepart(weekday, @DATE)%3)%2>0 THEN 1 ELSE 2 END ELSE 0 END WHEN 4 THEN CASE WHEN datepart(weekday, @DATE)>2 THEN CASE WHEN (datepart(weekday, @DATE))%4<3 THEN 2 ELSE 1 END ELSE 0 END WHEN 5 THEN CASE WHEN datepart(weekday, @DATE)>1 THEN CASE WHEN (datepart(weekday, @DATE))IN (2,7) THEN 1 ELSE 2 END ELSE 0 END WHEN 6 THEN CASE WHEN (datepart(weekday, @DATE))IN (1,7) THEN 1 ELSE 2 END END '周末天数'
SELECT datename(weekday, '2009-10-15') [星期]
星期
------------------------------
星期四(所影响的行数为 1 行)
@begintime datetime,
@endtime datetime
)
returns int
as
begin
declare @n int
set @N=0
set datefiRst 1
while(@begintime<=@endtime)
begin
if(select datepart(weekday,@begintime)=7)
set @n=@n+1
set @begintime=dateadd(day,1,@begintime)
end
return @n
end
SELECT @begin_date = '2009-08-01',@end_date = '2009-08-31';DECLARE @days INT,@i INT;SELECT @i= DATEDIFF(day,@begin_date,@end_date)+1,
@days = @i/7*2,
@begin_date=DATEADD(Day,@i/7*7,@begin_date);WHILE @begin_date<=@end_date
SELECT
@days=CASE WHEN
(DATEPART(weekday,@begin_date) + @@DATEFIRST - 1) % 7 IN(0,6)
THEN @days+1 ELSE @days END,
@begin_date=DATEADD(day,1,@begin_date);SELECT @days AS 周末天数;
/*
周末天数
-----------
10(1 行受影响)
*/
RETURNS INT
AS
BEGIN
DECLARE @DAYS INT,@I INT;
SELECT @I= DATEDIFF(DAY,@BT,@ET)+1,
@DAYS = @I/7*2,
@BT=DATEADD(DAY,@I/7*7,@BT);
RETURN @DAYS
END
GO
select dbo.F_WEEKDAY('2009-08-01','2009-08-31')/*-----------
8(1 行受影响)
*/小梁已经写出来了,封装一下即可.
@begintime datetime,
@endtime datetime
)
returns int
as
begin
declare @n int
set @N=0
while(@begintime<=@endtime)
begin
if(select datepart(weekday,@begintime))=7 or(select datepart(weekday,@begintime))=6
set @n=@n+1
set @begintime=dateadd(day,1,@begintime)
end
return @n
end
set datefirst 1
DECLARE @begintime DATETIME,@endtime DATETIME;
SELECT @begintime = '2009-08-01',@endtime = '2009-08-31';
select dbo.kk1(@begintime,@endtime)
/*
-----------
10*/
--菜鸟水平,抛砖引玉
set datefiRst 1DECLARE @DATE DATETIME
DECLARE @DAY INT
SELECT @DATE='2009-08-07',@DAY=7
SELECT @DATE,datepart(weekday, @DATE) 'DATEPART',datename(weekday, @DATE)
,@DAY%7 '%'
,@DAY/7 '/'
,(@DAY/7)*2+CASE @DAY%7
WHEN 0 THEN 0
WHEN 1 THEN
CASE WHEN datepart(weekday, @DATE)>5 THEN 1 ELSE 0 END
WHEN 2 THEN
CASE WHEN datepart(weekday, @DATE)>5 THEN 8-datepart(weekday, @DATE) ELSE 0 END
WHEN 3 THEN
CASE WHEN datepart(weekday, @DATE)>3 THEN CASE WHEN (datepart(weekday, @DATE)%3)%2>0 THEN 1 ELSE 2 END ELSE 0 END
WHEN 4 THEN
CASE WHEN datepart(weekday, @DATE)>2 THEN CASE WHEN (datepart(weekday, @DATE))%4<3 THEN 2 ELSE 1 END ELSE 0 END
WHEN 5 THEN
CASE WHEN datepart(weekday, @DATE)>1 THEN CASE WHEN (datepart(weekday, @DATE))IN (2,7) THEN 1 ELSE 2 END ELSE 0 END
WHEN 6 THEN
CASE WHEN (datepart(weekday, @DATE))IN (1,7) THEN 1 ELSE 2 END
END
--正式版
set datefiRst 1DECLARE @DATE DATETIME,@DAY INT
SELECT @DATE='2009-08-07',@DAY=7
SELECT @DATE '当前日期',datename(weekday, @DATE) '当前星期'
,(@DAY/7)*2+CASE @DAY%7
WHEN 0 THEN 0
WHEN 1 THEN
CASE WHEN datepart(weekday, @DATE)>5 THEN 1 ELSE 0 END
WHEN 2 THEN
CASE WHEN datepart(weekday, @DATE)>5 THEN 8-datepart(weekday, @DATE) ELSE 0 END
WHEN 3 THEN
CASE WHEN datepart(weekday, @DATE)>3 THEN CASE WHEN (datepart(weekday, @DATE)%3)%2>0 THEN 1 ELSE 2 END ELSE 0 END
WHEN 4 THEN
CASE WHEN datepart(weekday, @DATE)>2 THEN CASE WHEN (datepart(weekday, @DATE))%4<3 THEN 2 ELSE 1 END ELSE 0 END
WHEN 5 THEN
CASE WHEN datepart(weekday, @DATE)>1 THEN CASE WHEN (datepart(weekday, @DATE))IN (2,7) THEN 1 ELSE 2 END ELSE 0 END
WHEN 6 THEN
CASE WHEN (datepart(weekday, @DATE))IN (1,7) THEN 1 ELSE 2 END
END '周末天数'