如何求得一个月的最后一个工作日？语句越简单越好，谢谢了！
假如今天是2007.04.18，我想得到的是2007.04.30
假如今天是2007.06.10，我想得到的是2007.06.29
假如今天是2007.09.10，我想得到的是2007.09.28
[解决办法]
--楼主试试这个,不知道我这个方法会不会太笨!
declare @date datetime
set @date= '2007-09-10 '
set @date=cast(convert(char(8),@date,120)+ '01 ' as datetime)
select max([date]) as [date] from (select dateadd(day,-1,dateadd(month,1,@date)) as [date]
union
select dateadd(day,-2,dateadd(month,1,@date))
union
select dateadd(day,-3,dateadd(month,1,@date)))t
where datename(weekday,[date]) not in ( '星期六 ', '星期日 ')
[解决办法]
declare @date varchar(10)
set @date= '2007.09.10 '
select case datename(weekday,dateadd(day,-1,convert(varchar(8),dateadd(month,1,@date),120)+ '01 '))
when '星期六 ' then dateadd(day,-2,convert(varchar(8),dateadd(month,1,@date),120)+ '01 ')
when '星期日 ' then dateadd(day,-3,convert(varchar(8),dateadd(month,1,@date),120)+ '01 ')
else dateadd(day,-1,convert(varchar(8),dateadd(month,1,@date),120)+ '01 ') end
[解决办法]
试试：
declare @date datetime
set @date = '2007-04-10 '
declare @d datetime,@d1 datetime
set @d = dateadd(month,1+datediff(month,0,@date),0)
select @d - power(0, datepart(weekday,@d)/3) * datepart(weekday,@d) -1
或：
select dateadd(month,1+datediff(month,0,getdate()),0)
- power(0, datepart(weekday,dateadd(month,1+datediff(month,0,getdate()),0))/3) * datepart(weekday,dateadd(month,1+datediff(month,0,getdate()),0)) -1
[解决办法]
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N '[DBO].[pz_GetLastJobDay] ') AND OBJECTPROPERTY(ID, N 'ISPROCEDURE ') = 1)
DROP PROCEDURE [DBO].[pz_GetLastJobDay]
GO
CREATE PROC pz_GetLastJobDay
@Date DATETIME
AS
declare @lastday datetime
set @lastday=dateadd(dy,-day(@Date)+1,@Date)
set @lastday=dateadd(mm,1,@lastday)
set @lastday=cast(datepart(year,@lastday)as nvarchar(10))+ '- '+cast(datepart(month,@lastday)as nvarchar(10))+ '- '+ '01 00:00:00 '
set @lastday=dateadd(ss,-1,@lastday)
while datename(weekday,@lastday) like '%[六日] '
begin
set @lastday=dateadd(dd,-1,@lastday)
end
select convert( nvarchar(10),@lastday ,120)
---------
GO
exec pz_GetLastJobDay '2007-04-17 '
GO
exec pz_GetLastJobDay '2007.06.10 '
GO
exec pz_GetLastJobDay '2007.09.10 '
[解决办法]
改進一下:
CREATE PROC pz_GetLastJobDay
@Date DATETIME
AS
declare @lastday datetime
--得到這個月的最後一天
set @lastday=dateadd(dd,-day(@date),dateadd(mm,1,@date))
while datename(weekday,@lastday) = 'Saturday ' or datename(weekday,@lastday) = 'sunday '
begin
set @lastday=dateadd(dd,-1,@lastday)
end
select convert( nvarchar(10),@lastday ,120)
