CODEWITHSUNDEEP
sql-server
Coder157
Coder157

Reputation: 73

Converting different date formats to single one in SQL Server

One of the STRING column of my table has both date formats "mm-dd-yyyy" and "yyyy-mm-dd". While selecting data I am trying to cast it to DATE format but it is giving me conversion error. I think this is because SQL can not convert 2 different formats at a time. How can I achieve this to get both formats converted to DATE in one go.

Upvotes: 1

Views: 3189

Answers (2)

P Kernel
P Kernel

Reputation: 247

If I am correct ,In one go u cannot cast DATE into two different format @ the same time. However looking into your question I would suggest you to analyse all the distinct DATE FORMATS in your column and try to write query as written below:

DECLARE @DATECol TABLE (DATEVAL varchar(20))
INSERT @DATECol VALUES ('2016-07-01'),('01/07/2016'),('01JULY2016'),('19/07/16'),('28-08-2008')

select 
    CASE 
        WHEN ISNUMERIC(left(DATEVAL,4)) = 1 then CAST(DATEVAL as DATE) 
        WHEN LEN(DATEVAL) = 10 then CONVERT(DATE, DATEVAL, 103) 
        WHEN LEN(DATEVAL) = 8 then CONVERT(DATE, DATEVAL, 3) 
        WHEN CHARINDEX('/',DATEVAL) = 0 and ISNUMERIC(DATEVAL) = 0 then CONVERT(DATE, DATEVAL, 106) 
    END AS [Date] FROM @DATECol

Refer Link below for different DATE output formats http://www.w3schools.com/sql/func_convert.asp

Hope this Helps!

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453358

You should use the date datatype rather than storing these as strings anyway.

But you will still need code to convert to that.

You can use a CASE expression to toggle the style between 110 and 120

WITH T(Col) As
(
SELECT '04-01-2010' UNION ALL
SELECT '2010-04-01'
)
SELECT Col,
       CONVERT(date, Col,
       CASE WHEN COL LIKE '[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]' then 110 
            WHEN COL LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' then 120
        END
        )
FROM T

Upvotes: 4

Related Questions