LLI Wiki — My Digital Knowledge Base

Convertir une date au format britannique en DATETIME

Fonction scalaire T-SQL qui convertit une date jj/mm/aa en DATETIME, avec inversion jour/mois et gestion des années sur deux chiffres.

Cette fonction utilisateur convertit une date écrite au format britannique (jour/mois/année, par exemple 21/8/01) en valeur DATETIME. Elle accepte des jours et des mois sur un ou deux chiffres, et des années sur deux ou quatre chiffres.

Fonction

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

Exemple d'utilisation

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

Remarques

Mastodon LLIMastodon SocialFosstodon