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

Copy Code

SELECT dbo.StringToDate(ProjectStartDate)
FROM tblProject