CREATE  FUNCTION ufn_hr_getagefrombirthday

(

    @birthday DATE,

    @now DATE  =NULL  

)

RETURNS VARCHAR(20)

BEGIN  

    IF (@now IS NULL OR @now = '')

    BEGIN

        SET @now = GETDATE();

    END;  

    DECLARE @age INT;

    SET @age = 0;  

    DECLARE @result VARCHAR(20);  

    WHILE (DATEADD(YEAR, @age, @birthday) < @now)

    BEGIN

        SET @age = @age + 1;

    END;  

    SELECT @result

        = CAST(CASE

                   WHEN (@age - 1) < 0 THEN

                       0

                   ELSE

        (@age - 1)

               END AS VARCHAR(4)) + '岁'

          + CAST(DATEDIFF(

                             DAY,

                             DATEADD(   YEAR,

                                        CASE

                                            WHEN (@age - 1) < 0 THEN

                                                0

                                            ELSE

                                        (@age - 1)

                                        END,

                                        DATEADD(DAY, 1, @birthday)

                                    ),

                             DATEADD(YEAR, @age, DATEADD(DAY, 1, @birthday))

                         ) + 1 - (DATEDIFF(DAY, @now, DATEADD(YEAR, @age, DATEADD(DAY, 1, @birthday)))) AS VARCHAR(4))

          + '/'

          + CAST(DATEDIFF(

                             DAY,

                             DATEADD(YEAR, @age - 1, DATEADD(DAY, 1, @birthday)),

                             DATEADD(YEAR, @age, DATEADD(DAY, 1, @birthday))

                         )

AS      VARCHAR(4))  + '';  

    RETURN @result;

END;