在sql server中找到下一个特定的某一天 [英] find the next specific day of month in sql server

问题描述

嗨


如何查找给定日期的下一个特定日期，例如
，当我指定jan 01的下一个31时，然后它应该返回jan 31，如果我指定01年1月31日，那么它应该返回到3月31日。





如果我指定给定的日期为28/05/2014和31，然后它应该返回下一个第31天，即2014年5月31日，如果我将当天作为28/06/2014那么它应该返回31/07 / 2014.





谢谢

Hi
how to find the next specific day of a given date,
for example, when I specify the next 31st of jan 01, then it should return jan 31, and if I specify next 31 st of feb 01, then it should return mar 31.


If I specify the given day as 28/05/2014, and 31, then it should return the next 31st day that means 31/05/2014, and if I give the day as 28/06/2014 then it should return 31/07/2014.


thanks

推荐答案

创建以下功能

Hi,

Create below function

Create FUNCTION [dbo].[DateReturn]
(
	@date date,
	@sel_day int
)  
RETURNS date
AS  
BEGIN 
		
	declare @MonthEnd date;

declare @day int ;
declare @month int;
declare @year int;
declare @moth_end_day int;

declare @next_month int;
declare @Return date;



select @month =left( convert(varchar,@date,101),2) ,@day =left(convert(varchar,@date,103),2), @year = right(convert(varchar,@date,103),4)

Select @MonthEnd = dateadd(mm, (@year - 1900) * 12 + @month , 1)-2
select @moth_end_day = left(convert(varchar,@MonthEnd,103),2)


if (@sel_day <= @moth_end_day )
begin
 select @Return = dateadd(mm, (@year - 1900) * 12 + @month -1,@sel_day-1)
end 

else
begin
	set @next_month = @month+1;

	if(@next_month > 12)
		begin
			set @next_month =1;
			set @year =@year+1
		end

	while @next_month != @month
	begin
		select @moth_end_day = left(convert(varchar,dateadd(mm, (@year - 1900) * 12 + @next_month , 1)-2 ,103),2)

		if (@sel_day <= @moth_end_day )
		begin
			select @Return = dateadd(mm, (@year - 1900) * 12 + @next_month -1,@sel_day-1)
			break;
		end 
		set @next_month =@next_month+1;
		if(@next_month > 12)
		begin
			set @next_month =1;
			set @year =@year+1
		end
	end

end 

return @Return;
END

它将返回您期望的结果。



样品输出

It will return you expecting result.

Sample out put

declare @date date = '20140430';
select [dbo].[DateReturn](@date,31)

返回2014-05-31

It return 2014-05-31

参考这个

如何在C＃.net中选择月份的最后一个日期？ [ ^ ]



http://stackoverflow.com/questions / 3916398 / sql-server-2005-get-first-and-last-date-for-any-month-in-any-year- [ ^ ] br />


http://blog.sqlauthority.com/2007 / 05/13 / sql-server-query-to-find-first-last-last-of-current-month / [ ^ ]

Refer this
How to get selected month's last date in C#.net?[^]

http://stackoverflow.com/questions/3916398/sql-server-2005-get-first-and-last-date-for-any-month-in-any-year[^]

http://blog.sqlauthority.com/2007/05/13/sql-server-query-to-find-first-and-last-day-of-current-month/[^]

您好检查此解决方案：

Hi Check this Solution :

Declare @month int, -- Declared to select your month (5th month ,6th Month)
 @year int, -- year need to be checked
 @NoofDays int,  -- Here you give your days 30 or 31 
 @ChkNoofDays int,-- get the no of days in selected year and Month
 @ChkNoofDays_FEB int; -- feb no of days check 
Declare @first DateTime, 
@last DateTime,@last_WITH31 DateTime; --Declared this to store the result
Set @month=5; -- selected month as 5(may)
SET @NoofDays=31;  -- No of days need to check that is end date here example 31 or 30
Set @year=2014;  -- selected year
Set @first=CAST(CAST(@year AS varchar) + '-' + CAST(@month AS varchar) + '-' + '1' AS DATETIME);  -- first day
Set @ChkNoofDays=datediff(day, 
		dateadd(day, 0, dateadd(month, ((@year - 1900) * 12) + @month - 1, 0)),
		dateadd(day, 0, dateadd(month, ((@year - 1900) * 12) + @month, 0))
		) -- get the No of days per month
Set @ChkNoofDays_FEB=datediff(day, 
		dateadd(day, 0, dateadd(month, ((@year - 1900) * 12) + @month - 1, 0)),
		dateadd(day, 0, dateadd(month, ((@year - 1900) * 12) + @month, 0))
		)  -- check for the Feb and add one month 
		
Set @last=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@first)+1,0)); -- last day of Month
select @last_WITH31= CASE WHEN @ChkNoofDays=28 THEN -- case first check for feb month
 DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@first)+2,0)) -- if its feb then add 2 month that is March
		
	ELSE -- if its not feb then 
	Case When @NoofDays=@ChkNoofDays then DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@first)+1,0)) --case check for the  noofday you provided might be 30 or 31 in your case
	
		ELSE DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@first)+2,0)) END
		END
SELECT @first First_Day,@last last_Day,@last_WITH31 LASTDAY_WithResult; -- check the result 
-- you can test with different month year and noofdays

这篇关于在sql server中找到下一个特定的某一天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！