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
- The input must contain exactly two
/separators. Other values fail with a substring or conversion error. - If the second number (the month) is greater than 12, the two numbers are swapped:
8/21/01gives 21 August 2001. - Two-digit years below
@boundary(20) give 20xx; the others give 19xx. Change@boundaryto move the cut-off. - As a scalar function, it runs row by row and can slow down large queries.