比如说该月，每星期一到星期五上班，每星期六日休息，如果用ＳＱＬ语句生成这一个月的上班表,
------解决方案--------------------
select DATEADD(day,number,convert(varchar(7),GETDATE(),120)+'-01') as a,
case when DATEPART(WEEKDAY,DATEADD(day,number,convert(varchar(7),GETDATE(),120)+'-01')) not in (1,7) then '上班'
else '放假' end as 安排
from master.dbo.spt_values
where type='p'
其它法定假期得另算
------解决方案--------------------
异曲同工:
declare @month datetime = '2014-1-1'
declare @days int
set @month = DATEADD(day, 1-datepart(day, @month), @month)
set @days = DATEDIFF(day, @month, dateadd(MONTH, 1, @month))
;
with day as (
select DATEADD(day,number,@month) date,DATEPART(weekday, DATEADD(day,number,@month)) week from master..spt_values where type='P' and number<@days
)
select date, case when week=1 or week=7 then '休息' else '上班' end from day
------解决方案--------------------
DECLARE @ym CHAR(6)
SET @ym='201401'
;WITH a1 AS
(
SELECT CAST(@ym+'01' AS DATETIME) date_d
UNION ALL
SELECT DATEADD(d,1,date_d) FROM a1
WHERE CONVERT(CHAR(6),DATEADD(d,1,date_d),112)=@ym
)
,a2 AS
(
SELECT *,DATEPART(weekday,date_d) week_day
FROM a1
)
SELECT *,CASE WHEN week_day IN (1,7) then '休息' else '上班' end
FROM a2
------解决方案--------------------
with t as
(select dateadd(d,number,stuff(convert(varchar,getdate(),23),9,2,'01')) 'd'
from master.dbo.spt_values
where type='P' and
datediff(m,
stuff(convert(varchar,getdate(),23),9,2,'01'),
dateadd(d,number,stuff(convert(varchar,getdate(),23),9,2,'01')))=0)
select convert(varchar(20),d,23) 'd',
case datepart(dw,d) when 1 then '星期日'
when 2 then '星期一'
when 3 then '星期二'
when 4 then '星期三'
when 5 then '星期四'
when 6 then '星期五'
when 7 then '星期六' end 'w',
case when datepart(dw,d) in(1,7) then '休息'
when datepart(dw,d) in(2,3,4,5,6) then '上班' end 'x'
from t
/*
d w x
-------------------- ---------- ----------
2014-01-01 星期三 上班
2014-01-02 星期四 上班
2014-01-03 星期五 上班
2014-01-04 星期六 休息
2014-01-05 星期日 休息
2014-01-06 星期一 上班