-- Collection of code snippets by Arne Vajhøj 
-- posted to eksperten.dk, usenet and other places (2002-now) 
CREATE FUNCTION udf_birthday_1(@cpr CHAR(10)) RETURNS DATETIME AS
BEGIN
    RETURN CONVERT(DATETIME,
    CASE CONVERT(INTEGER, SUBSTRING(@cpr,7,1))
        WHEN 0 THEN '19'
        WHEN 1 THEN '19'
        WHEN 2 THEN '19'
        WHEN 3 THEN '19'
        WHEN 4 THEN
            CASE
                WHEN CONVERT(INTEGER, SUBSTRING(@cpr,5,2)) < 37 THEN '20'
                ELSE '19'
            END
        WHEN 5 THEN
            CASE
                WHEN CONVERT(INTEGER, SUBSTRING(@cpr,5,2)) < 58 THEN '20'
                ELSE '18'
            END
        WHEN 6 THEN
            CASE
                WHEN CONVERT(INTEGER, SUBSTRING(@cpr,5,2)) < 58 THEN '20'
                ELSE '18'
            END
        WHEN 7 THEN
            CASE
                WHEN CONVERT(INTEGER, SUBSTRING(@cpr,5,2)) < 58 THEN '20'
                ELSE '18'
            END
        WHEN 8 THEN
            CASE
                WHEN CONVERT(INTEGER, SUBSTRING(@cpr,5,2)) < 58 THEN '20'
                ELSE '18'
            END
        WHEN 9 THEN
            CASE
                WHEN CONVERT(INTEGER, SUBSTRING(@cpr,5,2)) < 37 THEN '20'
                ELSE '19'
            END
    END
    + SUBSTRING(@cpr,5,2) + '-' + SUBSTRING(@cpr,3,2) + '-' + SUBSTRING(@cpr,1,2), 102)
END
GO

CREATE FUNCTION udf_birthday_2(@cpr CHAR(10)) RETURNS DATETIME AS
BEGIN
    DECLARE @yy CHAR(2), @mm CHAR(2), @dd CHAR(2)
    DECLARE @bnum INTEGER
    DECLARE @yyyy CHAR(4)
    SET @dd = LEFT(@cpr, 2)
    SET @mm = SUBSTRING(@cpr, 3, 2)
    SET @yy = SUBSTRING(@cpr, 5, 2)
    SET @bnum = SUBSTRING(@cpr, 7, 4)
    IF @bnum between 4000 and 4999 and @yy < 37
        SET @yyyy = '20' + @yy
    ELSE
        SET @yyyy = '19' + @yy
    RETURN CAST(@yyyy + @mm + @dd AS DATETIME)
END
GO

CREATE ASSEMBLY cpr FROM 'C:\CPR.dll' WITH PERMISSION_SET = SAFE
GO

CREATE FUNCTION udf_birthday_3(@cpr NCHAR(10)) RETURNS DATETIME AS
EXTERNAL NAME cpr.[E.CPR].Birthday
GO

SELECT dbo.udf_birthday_1('1104713145'),dbo.udf_birthday_2('1104713145'),dbo.udf_birthday_3('1104713145')
GO
SELECT dbo.udf_birthday_1('1104114145'),dbo.udf_birthday_2('1104114145'),dbo.udf_birthday_3('1104114145')
GO
SELECT dbo.udf_birthday_1('1104353145'),dbo.udf_birthday_2('1104353145'),dbo.udf_birthday_3('1104353145')
GO
SELECT dbo.udf_birthday_1('1104124100'),dbo.udf_birthday_2('1104124100'),dbo.udf_birthday_3('1104124100')
GO
SELECT dbo.udf_birthday_1('1104126100'),dbo.udf_birthday_2('1104126100'),dbo.udf_birthday_3('1104126100')
GO
SELECT dbo.udf_birthday_1('1104826100'),dbo.udf_birthday_2('1104826100'),dbo.udf_birthday_3('1104826100')
GO

CREATE PROCEDURE usp_test AS
BEGIN
    DECLARE @n INTEGER, @rep INTEGER
    DECLARE @dt DATETIME, @startproc DATETIME, @endproc DATETIME
    SET @rep = 1
    WHILE @rep <= 3
    BEGIN
        SET @startproc = GETDATE()
        SET @n = 1
        WHILE @n <= 100000
        BEGIN
            SET @dt = dbo.udf_birthday_1('1104826100')
            SET @n = @n + 1
        END
        SET @endproc = GETDATE()
        PRINT '1: ' + CAST(DATEDIFF(ms, @startproc, @endproc) AS VARCHAR(6))
        SET @startproc = GETDATE()
        SET @n = 1
        WHILE @n <= 100000
        BEGIN
            SET @dt = dbo.udf_birthday_2('1104826100')
            SET @n = @n + 1
        END
        SET @endproc = GETDATE()
        PRINT '2: ' + CAST(DATEDIFF(ms, @startproc, @endproc) AS VARCHAR(6))
        SET @startproc = GETDATE()
        SET @n = 1
        WHILE @n <= 100000
        BEGIN
            SET @dt = dbo.udf_birthday_3('1104826100')
            SET @n = @n + 1
        END
        SET @endproc = GETDATE()
        PRINT '3: ' + CAST(DATEDIFF(ms, @startproc, @endproc) AS VARCHAR(6))
        SET @rep = @rep + 1
    END
END
GO

usp_test
GO

DROP PROCEDURE usp_test
GO
DROP FUNCTION udf_birthday_1
GO
DROP FUNCTION udf_birthday_2
GO
DROP FUNCTION udf_birthday_3
GO
DROP ASSEMBLY cpr
GO
