function GetAgeDetailFull in sqlserver

สร้าง custom function ชื่อ  GetAgeDetailFull


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create FUNCTION [dbo].[GetAgeDetailFull]  
(  
 @BirthDay DATETIME,  
 @ApplyDay DATETIME  
)  
RETURNS VARCHAR(100)  
AS  
BEGIN
DECLARE @Return VARCHAR(100)

IF (@BirthDay IS NULL OR @BirthDay >= @ApplyDay)
BEGIN
SET @Return = '0Y 0M 0D'
END
ELSE
BEGIN
DECLARE @IntervalMonth INT  
DECLARE @IntervalDay INT  
DECLARE @ISTodayAfter INT  
 
SET @IntervalDay = DATEDIFF(DAY, @BirthDay, @ApplyDay)  
SET @IntervalMonth = DATEDIFF(MONTH, @BirthDay, @ApplyDay)  
SET @ISTodayAfter = CASE   
  WHEN  
DATEDIFF(DAY,  
DATEADD(YEAR, DATEDIFF(YEAR, @BirthDay, @ApplyDay), @BirthDay),  
@ApplyDay) < 0
  THEN -1  
  ELSE 0  
 END  
 
SET @Return = CASE   
WHEN @IntervalDay < 0 THEN ''
WHEN (DATEDIFF(YEAR, @BirthDay, @ApplyDay)+@ISTodayAfter) > -1 THEN       
CONVERT(VARCHAR(10), DATEDIFF(YEAR, @BirthDay, @ApplyDay)+@ISTodayAfter)+'Y '+
CONVERT(VARCHAR(10), DATEPART(MONTH, DATEDIFF(DAY, @BirthDay, @ApplyDay))-1)+'M '+
CONVERT(VARCHAR(10), DATEPART(DAY, DATEDIFF(DAY, @BirthDay, @ApplyDay))-1)  + 'D'
END 
END
RETURN @Return
END

วิธีเรียก Select GetAgeDetailFull({FieldName},{FieldName}) as Age