declare @dateX datetime
set @dateX = '2003-5-3'set @datex=dateadd(month,1,@dateX-day(@dateX)+1)-1
select dateadd(day,case datepart(weekday,@datex) when 1 then -2 when 7 then -1 else 0 end,@datex)
set @dateX = '2003-5-3'set @datex=dateadd(month,1,@dateX-day(@dateX)+1)-1
select dateadd(day,case datepart(weekday,@datex) when 1 then -2 when 7 then -1 else 0 end,@datex)
select @weekday=datename(weekday,dateadd(dd,-1,cast(month(getdate())+1 as varchar(2)) +'/01/' + cast(year(getdate()) as varchar(4))))
print @weekday
if @weekday = 'Saturday'
print dateadd(dd,-1,dateadd(dd,-1,cast(month(getdate())+1 as varchar(2)) +'/01/' + cast(year(getdate()) as varchar(4))))
else if @weekday = 'Sunday'
print dateadd(dd,-2,dateadd(dd,-1,cast(month(getdate())+1 as varchar(2)) +'/01/' + cast(year(getdate()) as varchar(4))))
else
print dateadd(dd,-1,cast(month(getdate())+1 as varchar(2)) +'/01/' + cast(year(getdate()) as varchar(4)))
如果你要指定日期的话,可以把其中的getdate()改成你将要指定的日期,如:
declare @mydate datetime
set @mydate = '2003-04-23'之后,用@mydate来替换其中的getdate(),即可
其实应该是23号 :)我是这样想的,已知2003/01/05是星期天
如你想求2003/04的最个一个星期日 可把2003/05/01与2003/01/05天数相减 再整除7
得到一余数,再把2003/05/01向前推余数天就到了不过感觉最好做个function好点吧 :)用法
设置@input为任一一天(当然是2003/01/05的某一天,如前的某一天,则需要小改动)
declare @input smalldatetime
declare @output smalldatetime
set @input='2004/2/05'declare @nextmonth smalldatetime
set @nextmonth=cast(year(dateadd(month,1,@input)) as varchar(4))+'/'+cast(month(dateadd(month,1,@input)) as varchar(2))+'/01'declare @i int
set @i=-DATEDIFF(day, '2003/01/05',@nextmonth)%7
if @i=0 set @i=-7set @output=dateadd(day,@i ,@nextmonth)
select @output
用法
设置@input为任一一天(当然是2003/01/05后的某一天,如前的某一天,则需要小改动)