CODEWITHSUNDEEP
dateformathanato-date
Felipe Morales
Felipe Morales

Reputation: 3

to_date in HANA with mixed date formats

What can you do in case you have different date formats in the origin?

I have a case where we are using a to_date function to get the information from a table, but I am getting an error because some of the records have a date format YYYY-DD-MM instead of YYYY-MM-DD

How to apply a uniform solution for this?

Upvotes: 0

Views: 5581

Answers (2)

Abdel Dadouche
Abdel Dadouche

Reputation: 217

Why not use a case when in the select where you would test the different regular expressions, then use the to_date to return the date with the proper format.

This would avoid a union all and 2 select statements.

You could add more "format" without more "select" in an additional union.

Unless like_regexpr only works in where clause (I have to admit I never tried that function).

Upvotes: 0

Lars Br.
Lars Br.

Reputation: 10396

To handle this situation (arbitrary text should be converted into a structured date value), I would probably work with regular expressions.

That way you can select the set of records that fit the format you like to support and perform the type conversion on those records.

For example:

create column table date_vals (dateval nvarchar (4000), date_val date)

insert into date_vals values ('2018-01-23', NULL);
insert into date_vals values ('12/23/2016', NULL);

    select dateval, to_date(dateval, 'YYYY-MM-DD') as SQL_DATE
    from date_vals
    where 
        dateval like_regexpr '[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}'

    union all

    select dateval, to_date(dateval, 'MM/DD/YYYY') as SQL_DATE
    from date_vals
    where 
        dateval like_regexpr '[[:digit:]]{2}/[[:digit:]]{2}/[[:digit:]]{4}';

This approach also provides a good option to review the non-matching records and possible come up with additional required pattern.

Upvotes: 2

Related Questions