CODEWITHSUNDEEP
sql-serversql-server-2012
Sunil Kumar
Sunil Kumar

Reputation: 949

check if a date exists for a particular month in sql server

I am working on a project where I need to set plan's start and end dates.

Plans are to be setup in an order so that plan's renewal date should be same for all months(or after desired months).

If a plan(monthly) starts on 11th of june, its expiry date should be 10 july so that it can continue from next 11 and then next 11 and so on. And if plan is annual for the same startdate it should expire on 10th of june so that new start date would be 11th of june again and so on.

Here I need to check if expiry date does not exists, like if a plans starts from 31st of jan then we don't have a date 30th feb(its expiry date) in this scenario the expiry date should be set to 28 of feb and if a leap year should be 29th feb(last of invalid date's month).

For the same I am looking for a sql server udf function which except 2 parameters a datetime and number of months. Here it should return a new date(expiry date) after adding months(which is 2nd parameter).

Trying below but not getting the logic how to proceed it. 

  --print dbo.[Bill_FnDateFormatPackageEndDate]('2018-06-11 12:25',1)    
CREATE function [dbo].[Bill_FnDateFormatPackageEndDate]    
(    
@datetime datetime,    
@months int    
)    
returns nvarchar(40)    
begin    
declare @date nvarchar(40)   
declare @testdate nvarchar(40)     
declare @day nvarchar(2)    
declare @month nvarchar(2)    
declare @year nvarchar(4)   
declare @time nvarchar(10)    

 if ISDATE(@datetime)=1
   begin
    set @day = day(@datetime)
    set @month = month(@datetime)+1
    set @year = year(@datetime)

    set @testdate =@year+'/'+@month+'/'+@day
    if ISDATE(@testdate)=1
    set @date=DATEADD(day, -1, @testdate ) 
   end
return @date    
end

Upvotes: 1

Views: 2801

Answers (2)

Pratheek
Pratheek

Reputation: 81

This query will give you the last date based on the startdate and the number of months. You can convert this into a UDF.

select case when day(DATEADD(month, @nMonths, @StartDate)) = day(@StartDate) then dateadd(day, -1, DATEADD(month, @nMonths, @StartDate)) else DATEADD(month, @nMonths, @StartDate) end

Upvotes: 1

Sunil Kumar
Sunil Kumar

Reputation: 949

I have solved it, may be not the best of solutions but its working as i needed.

--print dbo.[Bill_FnDateFormatPackageEndDate]('2018-06-09 0:00',1)    
Alter function [dbo].[Bill_FnDateFormatPackageEndDate]    
(    
@startdate datetime,    
@months int    
)    
returns nvarchar(40)    
begin    
declare @date nvarchar(40)   
declare @testdate datetime     
declare @day int   
declare @month int
declare @year int
declare @NoOfDays int

   if ISDATE(@startdate)=1
   begin
    set @day = day(@startdate)
    set @month = month(@startdate)+@months
    set @year = year(@startdate)
    if @month>12
    begin
        set @month=@month-12
        set @year=@year+1
    end
    set @testdate=cast( (cast(@year as nvarchar(4))+'-'+cast(@month as nvarchar(2))+'-'+cast(1 as nvarchar(2))+' 0:00') as datetime)
    set @NoOfDays= datediff(day, dateadd(day, 1-day(@testdate), @testdate), dateadd(month, 1, dateadd(day, 1-day(@testdate), @testdate)))

    if @NoOfDays>=@day
    begin
        set @testdate =cast( cast((cast(@year as nvarchar(4))+'-'+cast(@month as nvarchar(2))+'-'+cast(@day as nvarchar(2))+' 0:00') as nvarchar(40)) as datetime)
        set @date=DATEADD(day, -1, cast(@testdate as datetime)) 
    end
    else     
         set @date =cast( cast((cast(@year as nvarchar(4))+'-'+cast(@month as nvarchar(2))+'-'+cast(@NoOfDays as nvarchar(2))+' 0:00') as nvarchar(40)) as datetime)            
   end
return @date--@date    
end

Upvotes: 0

Related Questions