How to update Different Date formats to one specific datetime formate

Question

I need advise on conversion of date time, below you may see the example data with images for current and expected output

Input Table

Expected Output

Answer 1

You haven't mentioned an RDBMS, so assuming SQL Server, you need to use CONVERT and a style definition to interpret and convert the string into DATETIME:

DECLARE @Table TABLE (ID INT IDENTITY, Country VARCHAR(255), DateString VARCHAR(25))

INSERT INTO @Table
VALUES ('USA', '02/13/2018'),
    ('Germany', '02.12.2018'),
    ('England', '23/02/2011'),
    ('UAE', '08 Jun 2008')

SELECT *,
    CASE
        WHEN [Country] = 'USA' THEN CONVERT(DATETIME, [DateString], 101)
        WHEN [Country] = 'Germany' THEN CONVERT(DATETIME, [DateString], 103)
        WHEN [Country] = 'England' THEN CONVERT(DATETIME, [DateString], 104)
        ELSE TRY_CAST([DateString] AS DATETIME)
    END AS DateValue
FROM @Table

Results

ID  Country     DateString      DateValue
-------------------------------------------------------
1   USA         02/13/2018      2018-02-13 00:00:00.000
2   Germany     02.12.2018      2018-12-02 00:00:00.000
3   England     23/02/2011      2011-02-23 00:00:00.000
4   UAE         08 Jun 2008     2008-06-08 00:00:00.000

Note that any strings that you can't implicitly identify from the Country will be caught by the TRY_CAST and will return NULL if they cannot be accurately converted. These NULLs might be difficult to handle automatically, and this is where data cleansing is important, if you can clean up data at the source and better control the input formats then you will have less of these issues.

CONVERT Styles

Answer 2

You can use CAST() Function

select id,name,country,Date, CAST('02/03/2018' AS datetime) AS actualdate
from test1

Answer 3

select [Id], [Name], [Country], [Date],
  parse([Date] as datetime) as [ActualDate]
  from {your table}

Parse reference: https://learn.microsoft.com/en-us/sql/t-sql/functions/parse-transact-sql

You might need to make use of the culture setting, but I'm not sure it will work for you as your dates appear to be multi-cultural. Might need some kind of error catching and several passes with different cultures to get them all. So you'd be looking at creating and then updating a temp table or adding that new column to your original input table.