Geek Answers Handbook

How to standardize a column of mixed date formats in T-SQL

Get a table in SQL Server that contains a column varcharwith date data. Unfortunately, dates are in a whole host of different formats.

2012-05-01
27/05/2012 
07MAY2014
19/07/13

There may be others, but all that I have encountered so far.

I need to compress them into a column datetimein another table, so I am trying to select them as the standard date and time values. At first I thought it would be easy:

UPDATE myTable 
SET myDateColumn = CONVERT(DATETIME, myDateColumn, 103)
WHERE ISDATE(myDateColumn) = 0

But the problem is that SQL Server also considers dd/mm/yyit dd/mm/yyyyas separate formats. The first is code 3, and the last is code 103. So, no matter how I run this update, it suffocates in the opposite format.

, / , datetime?

+4
4

, . - :

declare @tab table (d varchar(20))
insert @tab values ('2012-05-01'),('27/05/2012'),('07MAY2014'),('19/07/13')

select 
    case 
        when isnumeric(left(d,4)) = 1 then cast(d as date) 
        when len(d) = 10 then convert(date, d, 103) 
        when len(d) = 8 then convert(date, d, 3) 
        when charindex('/',d) = 0 and isnumeric(d) = 0 then convert(date, d, 106) 
    end as [date]
from @tab

:

date
----------
2012-05-01
2012-05-27
2014-05-07
2013-07-19

, , , . , , datetime, , .

: :

update @tab
set d = 
    case 
        when isnumeric(left(d,4)) = 1 then cast(d as date) 
        when len(d) = 10 then convert(date, d, 103) 
        when len(d) = 8 then convert(date, d, 3) 
        when charindex('/',d) = 0 and isnumeric(d) = 0 then convert(date, d, 106) 
    end 
from @tab
+2

SQL Server 2012 try_convert(). :

UPDATE myTable 
    SET myDateColumn = CONVERT(DATETIME, myDateColumn, 103)
    WHERE ISDATE(myDateColumn) = 0 AND MyDateColumn like '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]';

UPDATE myTable 
    SET myDateColumn = CONVERT(DATETIME, myDateColumn, 3)
    WHERE ISDATE(myDateColumn) = 0 AND MyDateColumn like '[0-9][0-9]/[0-9][0-9]/[0-9][0-9]';

. where, , update. select. , case:

UPDATE myTable 
    SET myDateColumn = (CASE WHEN ISDATE(myDateColumn) = 0 AND MyDateColumn like '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]'

                             THEN CONVERT(DATETIME, myDateColumn, 103)
                             ELSE myDateColumn
                        END)
    WHERE ISDATE(myDateColumn) = 0 AND MyDateColumn like '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-0]'

, , , . datetime .

+1

, :

DECLARE @DodgyDates TABLE (
    DateString VARCHAR(50));
INSERT INTO @DodgyDates VALUES ('2012-05-01');
INSERT INTO @DodgyDates VALUES ('27/05/2012'); 
INSERT INTO @DodgyDates VALUES ('07MAY2014');
INSERT INTO @DodgyDates VALUES ('19/07/13');
SELECT * FROM @DodgyDates;
--SELECT CONVERT(DATE, DateString) FROM @DodgyDates;--Fails
WITH DateDeconstruct AS (
    SELECT
        *,
        CASE
            WHEN DateString LIKE '____-__-__' THEN DateString
            WHEN DateString LIKE '__/__/____' THEN RIGHT(DateString, 4) + '-' + SUBSTRING(DateString, 4, 2) + '-' + LEFT(DateString, 2)
            WHEN DateString LIKE '__/__/__' THEN '20' + RIGHT(DateString, 2) + '-' + SUBSTRING(DateString, 4, 2) + '-' + LEFT(DateString, 2)
            WHEN DateString LIKE '_________' THEN RIGHT(DateString, 4) + '-' + CONVERT(VARCHAR(2), DATEPART(MM, DateString)) + '-' + LEFT(DateString, 2)
        END AS FixedString
    FROM
        @DodgyDates)
SELECT
    DateString AS OriginalDate,
    FixedString AS FixedDate,
    CONVERT(DATE, FixedString) AS ConvertedDate
FROM
    DateDeconstruct;

:

OriginalDate    FixedDate   ConvertedDate
2012-05-01  2012-05-01  2012-05-01
27/05/2012  2012-05-27  2012-05-27
07MAY2014   2014-5-07   2014-05-07
19/07/13    2013-07-19  2013-07-19
+1

, 110 USA date fromat, .

0

More articles:


All Articles