LLI Wiki — My Digital Knowledge Base

Convert a UK-style date string to DATETIME

T-SQL scalar function that converts a dd/mm/yy date string into a DATETIME value, with day/month swap and two-digit year handling.

This user-defined function converts a date written in UK style (day/month/year, for example 21/8/01) into a DATETIME value. It accepts one- or two-digit days and months, and two- or four-digit years.

Function

CREATE FUNCTION dbo.f_convert_str_to_date(@str VARCHAR(10))
RETURNS DATETIME
AS
BEGIN

    -- Declare local variables
    DECLARE @boundary TINYINT
    DECLARE @separator CHAR(1)
    DECLARE @return VARCHAR(11)
    DECLARE @index1 INT
    DECLARE @index2 INT
    DECLARE @day VARCHAR(2)
    DECLARE @month VARCHAR(2)
    DECLARE @year VARCHAR(4)
    DECLARE @iDay TINYINT
    DECLARE @iMonth TINYINT
    DECLARE @iYear SMALLINT
    DECLARE @iSwap TINYINT

    -- 'Constants'
    SELECT @boundary = 20
    SELECT @separator = '/'

    -- Indexes of forward slash separators (2 are expected)
    SELECT @index1 = CHARINDEX(@separator, @str)
    SELECT @index2 = CHARINDEX(@separator, @str, @index1 + 1)

    -- Get the day, month and year
    SELECT @day = LTRIM(RTRIM(LEFT(@str, @index1 - 1)))
    SELECT @month = LTRIM(RTRIM(SUBSTRING(@str, (@index1 + 1), (@index2 - @index1) - 1)))
    SELECT @year = LTRIM(RTRIM(RIGHT(@str, (LEN(@str) - @index2))))

    -- Convert the values to integer representations (will throw an error if they can't be converted)
    SELECT @iDay = CAST(@day AS TINYINT)
    SELECT @iMonth = CAST(@month AS TINYINT)
    SELECT @iYear = CAST(@year AS SMALLINT)

    -- Swap the day and month if they're obviously in the wrong format
    IF @iMonth > 12
    BEGIN
        SELECT @iSwap = @iMonth
        SELECT @iMonth = @iDay
        SELECT @iDay = @iSwap
    END

    -- Convert back to string representations
    SELECT @day = CAST(@iDay AS VARCHAR(2))
    SELECT @month = CAST(@iMonth AS VARCHAR(2))
    SELECT @year = CAST(@iYear AS VARCHAR(4))

    -- If the day and/or month and/or year are a single digit, prefix with a zero
    SELECT @day = CASE WHEN LEN(@day) = 1 THEN '0' + @day ELSE @day END
    SELECT @month = CASE WHEN LEN(@month) = 1 THEN '0' + @month ELSE @month END
    SELECT @year = CASE WHEN LEN(@year) = 1 THEN '0' + @year ELSE @year END

    -- If the year is only 2 digits long, prefix with '19' or '20' (depending on the boundary)
    IF LEN(@year) = 2 SELECT @year = CASE WHEN @iYear < @boundary THEN '20' + @year ELSE '19' + @year END

    -- Build the cleaned up date string, with the month number converted to a string expression
    SELECT @return = @day + '-'
        + CASE @month
            WHEN '01' THEN 'JAN'
            WHEN '02' THEN 'FEB'
            WHEN '03' THEN 'MAR'
            WHEN '04' THEN 'APR'
            WHEN '05' THEN 'MAY'
            WHEN '06' THEN 'JUN'
            WHEN '07' THEN 'JUL'
            WHEN '08' THEN 'AUG'
            WHEN '09' THEN 'SEP'
            WHEN '10' THEN 'OCT'
            WHEN '11' THEN 'NOV'
            WHEN '12' THEN 'DEC'
        END
        + '-' + @year

    -- Done
    RETURN CAST(@return AS DATETIME)

END

Example

SELECT dbo.f_convert_str_to_date('21/8/01')

Notes

Mastodon LLIMastodon SocialFosstodon