convert different string date formats from single column to one form of output in sql server-CodePudding

I have one date columns as varchar datatype which has multiple date formats. I have to convert all different formats into one date format as 'YYYY-MM-DD'. I am trying to convert it but couldn't make it. Below are different formats available in column.

Input

8/15/2022
15-Aug-22
15/08/2022

Required Output

2022-08-15

CodePudding user response：

You can use a combination of TRY_CONVERT and REPLACE function in a CASE operator to do so.

As an example :

DECLARE @T TABLE(STR_DATE VARCHAR(32));

INSERT INTO @T VALUES
('8/15/2022'),
('15-Aug-22'),
('15/08/2022');

SELECT CASE
          WHEN TRY_CONVERT(DATE, STR_DATE, 101) IS NOT NULL
             THEN CONVERT(DATE, STR_DATE, 101)
          WHEN TRY_CONVERT(DATE, REPLACE(STR_DATE, '-', ' '), 6)  IS NOT NULL
             THEN CONVERT(DATE, REPLACE(STR_DATE, '-', ' '), 6)
          WHEN TRY_CONVERT(DATE, STR_DATE, 103)  IS NOT NULL
             THEN CONVERT(DATE, STR_DATE, 103)
       END
FROM   @T

CodePudding user response：

Honestly, I think you need to take the pessimistic approach here and assume that, possibly for a lot of your data, you don't know what the value is meant to be. As I stated in the comments, if you have the value '01/12/2021' is that 1 December 2021 or 12 January 2021, how do you know, and more importantly how would SQL Server know? As such, for dates like this you don't know and therefore the value NULL is more appropriate that a guess.

Here I use 3 different formats, an implicit one, and then 2 explicit ones (MM/dd/yyyy and dd/MM/yyyy) Then I check if the MIN and MAX values match (NULL values are ignored for aggregation), and if they do return that value. If they don't then NULL, as what value the date is is ambiguous and therefore intentionally shown as an unknown value (NULL):

You can, if needed, add more styles to the below, but this should be enough for you to work with.

CREATE TABLE dbo.YourTable (ID int IDENTITY(1,1), --I assume you have a unique identifier
                            StringDate varchar(20));

INSERT INTO dbo.YourTable (StringDate)
VALUES('8/15/2022'), --Must be M/d/yyyy
      ('15-Aug-22'),
      ('15/08/2022'), --Must be dd/MM/yyyy
      ('12/01/2021'); --Could be MM/dd/yyyy or dd/MM/yyyy
GO

SELECT YT.ID,
       YT.StringDate,
       CASE MAX(V.SomeDate) WHEN MIN(V.SomeDate) THEN MAX(V.SomeDate) END AS DateDate
FROM dbo.YourTable YT
     CROSS APPLY (VALUES(TRY_CONVERT(date,YT.StringDate)), --Implicit conversion
                        (TRY_CONVERT(date,YT.StringDate,101)), --US style MM/dd/yyyy
                        (TRY_CONVERT(date,YT.StringDate,103)))V(SomeDate) --UK style dd/MM/yyyy
GROUP BY YT.ID,
         YT.StringDate;

GO
DROP TABLE dbo.YourTable;