Convert A String Representation Of A UK Date To A Datetime

• Tweet
• 

        This function is not particularly smart - it expects a UK-style date string in 'dd/mm/yyyy' format, but can cope with 'dd/mm/yy' (and if mm > 12 it assumes the date is 'mm/dd/yyyy'). This work is licensed under a Creative Commons Attribution 2.5 License.

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 of usage:

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

Tags:
• SQL
• sql-server
• sqlserver
• sql_server