Convert varchar value to date in SQL Server

1
votes

I'm converting varchar data to date in SQL server.

Table having data like below,

enter image description here

So it can have NULL value, proper formatted date and can have like 19900411and 04221995.

So I have tried something like below, but getting error.

SELECT CASE 
            WHEN ISNULL(CAST(Dob AS VARCHAR), '') = '' THEN NULL
            WHEN LEN(CAST(Dob AS VARCHAR)) = '8' THEN CONVERT(
                     VARCHAR(10),
                     CONVERT(date, RIGHT(Dob, 4) + LEFT(Dob, 2) + SUBSTRING(Dob, 3, 2)),
                     103
                 )
            ELSE CONVERT(VARCHAR, CAST(Dob AS CHAR(8)), 103)
       END
FROM   TableName
WHERE  Dob IS NOT NULL

Msg 241, Level 16, State 1, Line 3 Conversion failed when converting date and/or time from character string.

I wanted to get output as date format MM-dd-yyyy

Please help me! Thanks!

4
sql-serversql-server-2012sql-server-2008-r2varchardate-conversion
Do it in 2 steps - start by unifying your string formats, then convert to dates. If you can change the table to hold the date in a date column that would be best. - Zohar Peled
actually data would be imported to this table as flat file format so I cannot change it. not possible in 1 step? - pedram
Can you identify all date formats in your table? - Zohar Peled
not able to identify all dates, but I must have to handle dates like 04221995 - pedram
Well, is 12301123 December 30th 1123 or November 23rd 1230? It's crucial to be able to identify every format accurately or you end up with errors as the best case scenario or wrong dates in the worst case. - Zohar Peled

4 Answers

1
votes

Can you check this answer. This is work for Sqlserver 2012 and above.

Based on your discussion I created a sample data.

CREATE TABLE #A
( COL VARCHAR(10)
) 
INSERT INTO #A VALUES( NULL),('19900411'),('19900411'),('04-04-1976'),('10-30-1952')
insert into #A values ('04221995')
insert into #A values ('02222009 ')
select * from #a
 SELECT 
    case isdate(col ) when  1 then  CONVERT(VARCHAR, CONVERT(DATE, COL) , 105) 
        when 0 then  SUBSTRING(COL,1,2) + '-'+SUBSTRING(COL,3,2) + '-'+SUBSTRING(COL,5,4)  
        end
 FROM #A
0
votes

You can use this query for your problem. It simply convert your varchar column to formatted MM-dd-yyyy

select convert(varchar(10), cast('2011-09-28' as date), 101)
0
votes

I think, you need this:

SELECT CASE 
        WHEN ISNULL(CAST(Dob AS VARCHAR), '') = '' THEN NULL
        WHEN LEN(CAST(Dob AS VARCHAR)) = '8' THEN CONVERT(VARCHAR(10),
               CONVERT(date, RIGHT(Dob, 2) + '-' +  SUBSTRING(Dob, 5, 2) +'-' + LEFT(Dob, 4)),
               103)
        ELSE CONVERT(VARCHAR, CAST(Dob AS CHAR(10)), 103)
   END
FROM   tbl
WHERE  Dob IS NOT NULL

OUTPUT: 04/11/1990

SQL Fiddle DEMO: SQL DEMO

0
votes
    CREATE TABLE #A
    ( COL VARCHAR(10)
    ) 
    INSERT INTO #A VALUES( NULL),('19900411'),('19900411'),('04-04-1976'),('10-30-1952'),('19950422')

     SELECT DATESTRINGFIELD = CONVERT(VARCHAR, CONVERT(DATE, COL) , 101) FROM #A

output
04/11/1990
04/11/1990
04/04/1976
10/30/1952
04/22/1995