locked
How to Convert Store Procedure To Function In SQL ? RRS feed

  • Question

  • User1152553138 posted
    CREATE PROCEDURE GetYearMonthDayDiff
     @JoinDate datetime,
     @RelieveDate DATETIME
    AS
    BEGIN
    DECLARE @years VARCHAR(40)
    DECLARE @months VARCHAR(30)
    DECLARE @days VARCHAR(30)
    
    SELECT @years = datediff(year, @JoinDate, @RelieveDate) -- To find Years
    SELECT @months = datediff(month, @JoinDate, @RelieveDate) - (datediff(year, @JoinDate, @RelieveDate) * 12)
    
    	-- To Find Months
    SELECT @days = datepart(d, @RelieveDate) - datepart(d, @JoinDate) -- To Find Days
    
    	IF @days < 0 
    	BEGIN
    		
    		IF @months > 0 
    		BEGIN
    			SET @months = CASE 
    				WHEN @days < 0
    					AND @months - 1 <= 0
    					THEN 12
    				WHEN @days < 0
    					THEN @months - 1
    				ELSE @months
    				END
    		END
    		ELSE
    		BEGIN
    			
    			SET @months = 12 + @months
    			SET @years = @years - 1
    			PRINT @months			
    			
    		END		
    
    		DECLARE @day INT
    		DECLARE @lastdayOfPrevMonth DATETIME
    
    		SELECT @day = datepart(d, @RelieveDate)
    
    		SELECT @lastdayOfPrevMonth = dateadd(d, - @day + @days, @RelieveDate)
    
    		SELECT @days = datepart(d, @lastdayOfPrevMonth)
    
    		PRINT @day
    		PRINT @lastdayOfPrevMonth
    	END
    
    	SELECT @years + ' Years, ' + @months + ' Months, ' + @days + ' Days' YearMonthDay
    END;
    GO

    The same query with 2 parameters i need to create as function ?

    Thursday, October 27, 2016 4:25 AM

Answers

  • User77042963 posted
    CREATE function GetYearMonthDayDiff (
     @JoinDate datetime,
     @RelieveDate DATETIME)
     returnS 
      varchar(50)
    AS
    BEGIN
    DECLARE @years VARCHAR(40)
    DECLARE @months VARCHAR(30)
    DECLARE @days VARCHAR(30)
    
    SELECT @years = datediff(year, @JoinDate, @RelieveDate) -- To find Years
    SELECT @months = datediff(month, @JoinDate, @RelieveDate) - (datediff(year, @JoinDate, @RelieveDate) * 12)
    
    	-- To Find Months
    SELECT @days = datepart(d, @RelieveDate) - datepart(d, @JoinDate) -- To Find Days
    
    	IF @days < 0 
    	BEGIN
    		
    		IF @months > 0 
    		BEGIN
    			SET @months = CASE 
    				WHEN @days < 0
    					AND @months - 1 <= 0
    					THEN 12
    				WHEN @days < 0
    					THEN @months - 1
    				ELSE @months
    				END
    		END
    		ELSE
    		BEGIN
    			
    			SET @months = 12 + @months
    			SET @years = @years - 1
    					
    			
    		END		
    
    		DECLARE @day INT
    		DECLARE @lastdayOfPrevMonth DATETIME
    
    		SELECT @day = datepart(d, @RelieveDate)
    
    		SELECT @lastdayOfPrevMonth = dateadd(d, - @day + @days, @RelieveDate)
    
    		SELECT @days = datepart(d, @lastdayOfPrevMonth)
    
    	END
    
    	return (SELECT @years + ' Years, ' + @months + ' Months, ' + @days + ' Days' )
    END;
    GO
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 27, 2016 5:16 AM