Varchar Values To Date Format

In SQL Server 2014, I have a varchar column with multiple date formats: shipdate ----------- 12/29/2014 -- mm/dd/yyyy 5/20/15 -- mm/dd/yy 2012/05/22 -- yyyy/mm/dd

Solution 1:

Hmmmm . . . You really want to store dates as dates. In your case, you can use try_convert() and keep testing until it works:

select coalesce(try_convert(date, shipdate),
                try_convert(date, shipdate, 1),
                try_convert(date, shipdate, 101)
               ) as presumed_date

Solution 2:

CONVERT would work just fine, though the style would need to be 111.

SELECT CONVERT(VARCHAR(10),CAST(SRD.[ShipDate] AS DATETIME),111);

Your code sample shows dates, though no time; additionally, I don't see that you want to see a time as per your question. Hence, I would suggest that you actually convert it to a DATE instead of DATETIME - as shown in the query below.

SELECTCONVERT(VARCHAR(10),CAST(SRD.[ShipDate] ASDATE),111);

Solution 3:

SELECTCONVERT(VARCHAR(10),CAST(SRD.[ShipDate] ASDATE),111);

This should work because the style that your looking for is 111

Solution 4:

It is not clear whether you are trying to do this in a query or trying to migrate your data to a different column so my examples below need to be filled in and addapted to what your specific need is. (replace the ... as fit). You could apply a specific format and use try convert. Then iterate over your data set multiple times to catch all values.:

SET DATEFORMAT mdy;  
    -- ... 
    TRY_CONVERT(datetime2, [ShipDate]) ASResult;
    -- ...  
    GO

    SET DATEFORMAT dmy;
    -- ... 
    TRY_CONVERT(datetime2, [ShipDate]) ASResult;
    -- ...
    GO

    -- ...