find the next specific day of month in sql server

Question

5.00/5 (2 votes)

See more:

SQL-Server-2008R2

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

Posted 27-May-14 20:04pm

Jijutj

Updated 27-May-14 20:14pm

v3

Add a Solution

Comments

Nirav Prabtani 28-May-14 2:08am

Please elaborate more and be specific

[no name] 28-May-14 2:36am

http://www.codeproject.com/Questions/148322/How-to-get-selected-month-s-last-date-in-C-nethttp://www.codeproject.com/Questions/148322/How-to-get-selected-month-s-last-date-in-C-net

[no name] 28-May-14 2:36am

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

[no name] 28-May-14 2:37am

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

syed shanu 28-May-14 2:44am

Do you want to check only for the last day of month for example you said 31st means If the selected date of month has 31 days then return last day of month .if the selected date has 28 days or 30 days in month then it should returm last day of
add month

3 solutions

Solution 1

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/[^]

Posted 27-May-14 20:37pm

Dnyaneshwar@Pune

Solution 2

Hi Check this Solution :

SQL

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

Posted 27-May-14 21:14pm

syed shanu

Comments

Jijutj 28-May-14 4:51am

Hi, my requirement is, we need to return the next selected date, for example suppose our start date is 28/06/2014 and our selected date is 29 the it should return 29/06/2014, and if it is 31, then it should return 31/07/2014 because the next 31 is on July.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shanalal Kasim · Accepted Answer · 2014-05-28T00:15:00

Hi,

Create below function

SQL

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

SQL

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

It return 2014-05-31