one date field with two formats

[gyuvaraj]

Hi team,

I have one date field with two date formats, here i have to convert it into one date format field,

tried below things but when i changed to date the string dates are become blank.

load   date(string_date) as date, 

date(date#(string_date,'DD/MM/YYYY'),'DD/MM/YYYY') as string_date  from table_name

provided the screenshot for your reference,

Regards,

Yuvaraj

[Or]

It looks like your date format is actually MM/DD/YYYY?

[gyuvaraj]

Hi,

I have date with 2 different formats like MM/DD/YYYY, DD/MM/YYYY but i tried now like 
Date(Date#(string_date,'MM/DD/YYYY'), 'DD/MM/YYYY'), Date(Date#(string_datestring_date,'DD/MM/YYYY'), 'DD/MM/YYYY') but not working as expected.

[Or]

Both of these sound problematic even if they were written correctly - how would you know if e.g. 1/6/2021 is MM/DD or DD/MM? Either one could be correct. Is there another field that tells you which format the date is in?

[gyuvaraj]

Hi, thanks for the response.

team confirmed now it is MM/DD and also tried with below method its shows as expected.

Date(Floor(Date#(string_date, 'MM/DD/YYYY')), 'DD/MM/YYYY') as string_date