how to get Dateofbirth column into Dateformat(DD/MM/YYYY)

Question

i have a table which have column like this

Actual data    wanted data
0k1/01/1976 --01/01/1976
1-1-1991    --01/01/1991
08/04/1968i -- 08/04/1968
18-05-1987j --  18/05/1987
5d60        -- null
I 5 7 1931  -- Null
12/06/1981v -- 12/06/1981
7-12-2012k  -- 07/12/2012
6r6r6rr7t7t7t -- null

i have tried with PATINDEX and replace functions but unable to move forward

I so hope they are typos, I wouldn't even know how you could get data like that in a sql table if it is set up as a date field — Jeremy C., May 06 '15 at 08:47
With data that bad, are you sure it's worth trying to salvage any of it? Are you sure of what is meant by any of the ambiguous dates (both non-year values in the range 1-12 meaning it could be day/month or month/day)? — Damien_The_Unbeliever, May 06 '15 at 09:29

score 3 · Answer 1 · edited May 23 '17 at 10:24

Create this function

alter function [dbo].[RemoveAlphaCharacters](@Temp nvarchar(1000))
returns nvarchar(1000)
as
begin

    declare @KeepValues as nvarchar(50)
    set @KeepValues = '%[^0-9,/,-]%'
    while patindex(@KeepValues, @Temp) > 0
        set @temp = stuff(@Temp, patindex(@KeepValues, @Temp), 1, '')

    return @temp
end
go

Test

declare @tbl table
(
    DOB nvarchar(20)
)

insert into @tbl 
values('0k1/01/1976'), --01/01/1976
('1-1-1991'),    --01/01/1991
('08/04/1968i'), -- 08/04/1968
('18-05-1987j'), --  18/05/1987
('5d60'),        -- null
('I 5 7 1931'),  -- Null
('12/06/1981v'), -- 12/06/1981
('7-12-2012k'),  -- 07/12/2012
('6r6r6rr7t7t7t') -- null


select  DOB Original, dbo.RemoveAlphaCharacters(DOB) RemoveNumber
        , iif(isdate(dbo.RemoveAlphaCharacters(DOB)) = 1
        , convert(datetime, dbo.RemoveAlphaCharacters(DOB)), null) [DateTime]
from    @tbl

Result

Original        RemoveAlp       DateTime
0k1/01/1976     01/01/1976      1976-01-01 00:00:00.000  
1-1-1991        1-1-1991        1991-01-01 00:00:00.000  
08/04/1968i     08/04/1968      1968-08-04 00:00:00.000  
18-05-1987j     18-05-1987      NULL  
5d60            560             NULL  
I 5 7 1931      571931          NULL  
12/06/1981v     12/06/1981      1981-12-06 00:00:00.000    
7-12-2012k      7-12-2012       2012-07-12 00:00:00.000  
6r6r6rr7t7t7t   666777          NULL

reference: https://stackoverflow.com/a/1008566/1554116

people like you givs SO a meaning. Many Many thanks to be a part of it @Rawitas Krungkaew — Ankit Bajpai, May 06 '15 at 10:39
@mohan111 the *IFF* is sqlserver 2012. But it is easy to convert to 2005 — t-clausen.dk, May 06 '15 at 12:16

score 1 · Answer 2 · answered May 06 '15 at 12:15

This will work with SQL-server 2005+. The function is only called once instead of 3 times, so this is a bit faster.

SELECT
  DOB Original, 
  RemoveNumber,
  CASE WHEN isdate(RemoveNumber) = 1 
  THEN convert(datetime, RemoveNumber) END [DateTime]
FROM yourtable
CROSS APPLY(SELECT dbo.RemoveAlphaCharacters(DOB) RemoveNumber) x

You will need the function from Rawitas Krungkaew's answer

how to get Dateofbirth column into Dateformat(DD/MM/YYYY)

2 Answers2