Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

DZone's Guide to

# Convert A String Representation Of A UK Date To A Datetime

·
Free Resource

Comment (0)

Save
{{ articles[0].views | formatCount}} Views
```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')
``````
Topics:

Comment (0)

Save
{{ articles[0].views | formatCount}} Views

Opinions expressed by DZone contributors are their own.