0

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

Input Table enter image description here

Expected Output enter image description here

4
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

0

You can use CAST() Function

select id,name,country,Date, CAST('02/03/2018' AS datetime) AS actualdate
from test1
-1
select [Id], [Name], [Country], [Date],
  parse([Date] as datetime) as [ActualDate]
  from {your table}

Parse reference: https://docs.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.

3
  • Error converting string value '22-11-2018' into data type datetime using culture... I'm getting error like this while trying your query
    – Raja
    May 9 '19 at 8:26
  • 2
    Could you add some detail about why your solution should fix the OP problem? May 9 '19 at 9:32
  • Apparently it doesn't since he's trying to convert multiple cultures formats. I edited in a reference to parse that further describes the culture option and some advice on possibly handling multiple culture conversions in multiple passes. May 9 '19 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.