27,580
社区成员
发帖
与我相关
我的任务
分享
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getdate]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_getdate]
GO
/*--生成列表
生成指定日期段的日期列表
--邹建 2005.03(引用请保留此信息)--*/
/*--调用示例
--查询工作日
SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',0)
--查询休息日
SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',1)
--查询全部日期
SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',NULL)
--*/
CREATE FUNCTION dbo.f_getdate(
@begin_date Datetime, --要查询的开始日期
@end_date Datetime, --要查询的结束日期
@bz bit --@bz=0 查询工作日,@bz=1 查询休息日,@bz IS NULL 查询全部日期
)RETURNS @re TABLE(id int identity(1,1),Date datetime,Weekday nvarchar(3))
AS
BEGIN
DECLARE @tb TABLE(ID int IDENTITY(0,1),a bit)
INSERT INTO @tb(a) SELECT TOP 366 0
FROM sysobjects a ,sysobjects b
IF @bz=0
WHILE @begin_date<=@end_date
BEGIN
INSERT INTO @re(Date,Weekday)
SELECT Date,DATENAME(Weekday,Date)
FROM(
SELECT Date=DATEADD(Day,ID,@begin_date)
FROM @tb
)a WHERE Date<=@end_date
AND (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 BETWEEN 1 AND 5
SET @begin_date=DATEADD(Day,366,@begin_date)
END
ELSE IF @bz=1
WHILE @begin_date<=@end_date
BEGIN
INSERT INTO @re(Date,Weekday)
SELECT Date,DATENAME(Weekday,Date)
FROM(
SELECT Date=DATEADD(Day,ID,@begin_date)
FROM @tb
)a WHERE Date<=@end_date
AND (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 in(0,6)
SET @begin_date=DATEADD(Day,366,@begin_date)
END
ELSE
WHILE @begin_date<=@end_date
BEGIN
INSERT INTO @re(Date,Weekday)
SELECT Date,DATENAME(Weekday,Date)
FROM(
SELECT Date=DATEADD(Day,ID,@begin_date)
FROM @tb
)a WHERE Date<=@end_date
SET @begin_date=DATEADD(Day,366,@begin_date)
END
RETURN
END
GO
----------------------------------------------------------------------------------------
--要处理的代码,考勤中空值表示未打卡。
DECLARE @tab1 TABLE(CardNo INT ,Name NVARCHAR(25),Start_Work DATETIME ,End_Work DATETIME )
INSERT INTO @tab1
SELECT 1518665 , N'张三','2009-07-01 08:23:00.000' ,'2009-07-01 18:38:00.000'
UNION ALL SELECT 1518665 ,N'张三',NULL ,'2009-07-02 17:30:00.000'
UNION ALL SELECT 1518665 , N'张三','2009-07-03 08:08:00.000','2009-07-03 17:29:00.000'
UNION ALL SELECT 1518683 ,N'李四','2009-07-01 08:30:00.000' ,'2009-07-01 18:37:00.000'
UNION ALL SELECT 1518683 ,N'李四','2009-07-02 08:35:00.000' ,NULL
UNION ALL SELECT 1518683 ,N'李四','2009-07-04 10:09:00.000' ,'2009-07-04 18:06:00.000'
UNION ALL SELECT 1518683 ,N'李四',NULL , '2009-07-05 17:33:00.000'
UNION ALL SELECT 1518686 , N'王五', '2009-07-02 07:48:00.000', '2009-07-02 17:48:00.000'
UNION ALL SELECT 1518686 ,N'王五' , '2009-07-03 08:01:00.000','2009-07-03 18:11:00.000'
UNION ALL SELECT 1518686 ,N'王五' , '2009-07-06 07:51:00.000', '2009-07-06 18:40:00.000'
SELECT t.Name
,CONVERT(CHAR(10),s.Date,120) AS Date
,CONVERT(CHAR(8),RIGHT(t.Start_Work,8)) AS Start_Work
,CONVERT(CHAR(8),RIGHT(t.End_Work,8)) AS End_Work
,CASE s.Weekday WHEN 'Monday' THEN N'星期一'
WHEN 'Tuesday' THEN N'星期二'
WHEN 'Wednesday' THEN N'星期三'
WHEN 'Thursday' THEN N'星期四'
WHEN 'Friday' THEN N'星期五'
WHEN 'Saturday' THEN N'星期六'
WHEN 'Sunday' THEN N'星期天'
END AS Weekday
FROM dbo.f_getdate('2009-07-01','2009-07-06',null) AS s
LEFT JOIN @tab1 AS t
ON CONVERT(CHAR(10),s.Date,120) = CONVERT(CHAR(10),t.Start_Work,120)
OR CONVERT(CHAR(10),s.Date,120) = CONVERT(CHAR(10),t.End_Work,120)
ORDER BY s.Date ,t.CardNo
SELECT
DATEADD(DD,NUMBER,'2009-10-01')AS TIME
FROM
MASTER..SPT_VALUES
WHERE TYPE='P'
AND DATEADD(DD,NUMBER,'2009-10-01')<='2009-10-10'
TIME
------------------------------------------------------
2009-10-01 00:00:00.000
2009-10-02 00:00:00.000
2009-10-03 00:00:00.000
2009-10-04 00:00:00.000
2009-10-05 00:00:00.000
2009-10-06 00:00:00.000
2009-10-07 00:00:00.000
2009-10-08 00:00:00.000
2009-10-09 00:00:00.000
2009-10-10 00:00:00.000
(所影响的行数为 10 行)