查询指定时间段内的工作日

SELECT dbo.IT_T_WORKDAY(‘2017-01-12′,’2018-01-12’,”)

ALTER FUNCTION [dbo].[IT_T_WORKDAY]

–指定日期范围内工作日. by lzp
(
@sdate varchar(50),
@edate varchar(50),
@str VARCHAR(5000)
)
RETURNS VARCHAR(5000)
AS
BEGIN

SELECT @str=COUNT(*) FROM (SELECT t.yyyymmdd
FROM ( SELECT CONVERT (CHAR(10), DATEADD(dd, number, @sdate), 120) AS yyyymmdd,
DATEPART(dw, CONVERT (CHAR(10), DATEADD(dd, number, @sdate), 120)) wn
FROM master.dbo.spt_values AS spt
WHERE type = ‘p’
AND CONVERT (CHAR(10), DATEADD(dd, number, @sdate), 120) < = @edate ) t
WHERE t.wn IN ( 2, 3, 4, 5, 6 )
AND t.yyyymmdd NOT IN ( SELECT HDAY
FROM dbo.IT_T_HDAY )
UNION
SELECT td.TDAY
FROM dbo.IT_T_TDAY td
WHERE td.TDAY >= @sdate
AND td.TDAY <= @edate) t WHERE 1=1

RETURN @str
END

/*
Navicat SQL Server Data Transfer

工作日休假表

Target Server Type : SQL Server
Target Server Version : 105000
File Encoding : 65001

Date: 2018-01-26 10:59:35
*/

— —————————-
— Table structure for IT_T_HDAY
— —————————-
DROP TABLE [dbo].[IT_T_HDAY]
GO
CREATE TABLE [dbo].[IT_T_HDAY] (
[ID] int NOT NULL ,
[HDAY] date NULL ,
[REMARK] varchar(50) NULL
)

GO

— —————————-
— Indexes structure for table IT_T_HDAY
— —————————-

— —————————-
— Primary Key structure for table IT_T_HDAY
— —————————-
ALTER TABLE [dbo].[IT_T_HDAY] ADD PRIMARY KEY ([ID])
GO

/*
Navicat SQL Server Data Transfer

周末调休表

Target Server Type : SQL Server
Target Server Version : 105000
File Encoding : 65001

Date: 2018-01-26 10:59:54
*/

— —————————-
— Table structure for IT_T_TDAY
— —————————-
DROP TABLE [dbo].[IT_T_TDAY]
GO
CREATE TABLE [dbo].[IT_T_TDAY] (
[ID] int NOT NULL ,
[TDAY] date NULL ,
[REMARK] varchar(50) NULL
)

GO

— —————————-
— Indexes structure for table IT_T_TDAY
— —————————-

— —————————-
— Primary Key structure for table IT_T_TDAY
— —————————-
ALTER TABLE [dbo].[IT_T_TDAY] ADD PRIMARY KEY ([ID])
GO

IQSYS/framework