UDF to work out a date value from a string version held in different formats

Category SQL Sub Category Date And Time
Bookmark and Share
A string version of date can be entered differently which makes the translation to a proper date type difficult. This function will do this for you.
It takes a string version of a date expecting dd/mm/yyyy (uk based).

However it will also take into account spaces, tabs, 2 and 4 year dates and leading 0's in the day, month and year values.

So the following example for 31 March 2010 will be translated correctly:

31/03/2010
31/3/2010
31/3/10
31 / 03 / 2010

This can happen when unstructured text entries are made for dates.

Run this code below in query analyser to create the function StringToDate.

Copy Code
create function StringToDate(@StringDate nvarchar(10)) returns datetime as BEGIN DECLARE @Day int DECLARE @Month int DECLARE @Year int --get rid of tabs SET @StringDate = REPLACE(@StringDate, Char(9), '') --get rid of padding SET @StringDate = LTRIM(RTRIM(@StringDate)) --workout day as first characters that are found before first / DECLARE @SlashPos int --day SET @SlashPos = charindex('/', @StringDate) IF @SlashPos > 0 BEGIN SET @Day = CAST(LTRIM(RTRIM(SUBSTRING(@StringDate, 1, @SlashPos - 1)))AS INT) SET @StringDate = SUBSTRING(@StringDate,@SlashPos + 1, 9999) --month SET @SlashPos = charindex('/', @StringDate) IF @SlashPos > 0 BEGIN SET @Month = CAST(LTRIM(RTRIM(SUBSTRING(@StringDate, 1, @SlashPos - 1)))AS INT) SET @StringDate = SUBSTRING(@StringDate,@SlashPos + 1, 9999) --year SET @Year = CAST(LTRIM(RTRIM(@StringDate)) AS INT) --if year less than 100 assume 2 digit year entered so add 2000 IF @Year < 100 SET @Year = @Year + 2000 END END RETURN dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1) END go


This can then be used in commands such as

Copy Code
SELECT dbo.StringToDate(ProjectStartDate) FROM tblProject


To get a datetime version of the string ProjectStartDate. It could also be used to transfer string based date values into a datetime field.
Share the love
Bookmark and Share