Sqlserver计算本年度工作日

 --@StartDate 本年度第一天
  --@EndDate   本年度最后一天
  DECLARE @StartDate DATETIME = DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) 
  DECLARE @EndDate DATETIME = DATEADD(ms,-3,DATEADD(yy,DATEDIFF(yy,GETDATE()) + 1,0))  
  IF EXISTS ( SELECT    *
              FROM      tempdb..sysobjects
              WHERE     id = OBJECT_ID(‘tempdb..#tb_work‘) ) 
    BEGIN       
        DROP TABLE  #tb_work      
    END       
  CREATE TABLE #tb_work
    (
      RN INT,WorkDate DATE,--日期
      DeWeek NVARCHAR(10),--星期几
      IsWork NVARCHAR(10)  --是否上班
    )  
  INSERT    INTO #tb_work
            ( RN,WorkDate,DeWeek  
            )
            SELECT  ROW_NUMBER() OVER ( ORDER BY CAST(DATEADD(day,number,@StartDate) AS DATE) ),CAST(DATEADD(day,number,@StartDate) AS DATE) AS dt,DATENAME(WEEKDAY,@StartDate) AS DATE))
            FROM    master.dbo.spt_values
            WHERE   type = ‘P‘
                    AND number <= DATEDIFF(day,@StartDate,@EndDate)
                    
            SELECT * FROM #tb_work;

下面根据自己数据库配置的情况更新表中的“是否上班”的字段

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!