Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I've got a file with two date formats in it and I'd like to convert it to one format. I've copied some sample data below. As you can see, some of the dates are DD-MM-YYYY and some are D/M/YYYY. I'd like to format the date as MM/DD/YYYY, keep the timestamp format and also be able to create a separate field that is just the date in MM/DD/YYYY format.
31-01-2017 12:00:00 |
31-01-2017 12:00:00 |
31-01-2017 12:00:00 |
31-01-2017 12:00:00 |
31-01-2017 12:00:00 |
31-01-2017 12:00:00 |
31-01-2017 12:00:00 |
31-01-2017 12:00:00 |
31-01-2017 12:00:00 |
1/2/2017 0:00 |
1/2/2017 0:00 |
1/2/2017 0:00 |
1/2/2017 0:00 |
1/2/2017 0:00 |
1/2/2017 0:00 |
1/2/2017 0:00 |
1/2/2017 0:00 |
1/2/2017 0:00 |
May be this
Date(Floor(Alt(Num(DateField), Num(Date#(DateField, 'DD-MM-YYYY hh:mm:ss')), Num(Date#(DateField, 'D/M/YYYY h:mm')))), 'MM/DD/YYYY') as NewDateField
May be this
Date(Floor(Alt(Num(DateField), Num(Date#(DateField, 'DD-MM-YYYY hh:mm:ss')), Num(Date#(DateField, 'D/M/YYYY h:mm')))), 'MM/DD/YYYY') as NewDateField
Very nice, that worked great!
Thank you.
date(alt(date,Date#(date,'DD-MM-YYYY'),Date#(date,'MM-DD-YYYY'),Date#(date,'DD/MM/YYYY'),Date#(date,'MM/DD/YYYY')),'MM/DD/YYYY') as newdates,
this is great, Thanks
Daniel