Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a csv-file with 2 different time formats in one column.
24.03.2014 12:57 and
2014-03-24 14:42:05.093
How can I format it in one Format?
load
field,
date(alt(Date#(field,'DD.MM.YYYY hh:mm'), Date#(field,'YYYY-MM-DD hh:mm:ss.fff'))) as newfield
inline [
field
24.03.2014 12:57
2014-03-24 14:42:05.093
];
the same expression works in a chart
alt(Date#(field,'DD.MM.YYYY hh:mm'), Date#(field,'YYYY-MM-DD hh:mm:ss.fff'))
In Number tab of properties you can set the data format whatever want...
load
field,
date(alt(Date#(field,'DD.MM.YYYY hh:mm'), Date#(field,'YYYY-MM-DD hh:mm:ss.fff'))) as newfield
inline [
field
24.03.2014 12:57
2014-03-24 14:42:05.093
];
the same expression works in a chart
alt(Date#(field,'DD.MM.YYYY hh:mm'), Date#(field,'YYYY-MM-DD hh:mm:ss.fff'))
Hi,
use ALT()
check in Help menu.
Regards
If i understand correctly you want to do it while loading? if yes
try this
load
*,
Timestamp(alt(Date#(Date,'DD.MM.YYYY hh:mm'), Date#(Date,'YYYY-MM-DD hh:mm:ss.fff'))) as DateNew
inline [
Date
24.03.2014 12:57
2014-03-24 14:42:05.093
];
LOAD dt,Timestamp(alt(Timestamp#(trim(dt),'DD.MM.YYYY hh:mm'),Timestamp#(trim(dt),'YYYY-MM-DD hh:mm:ss[.fff]'))) as newDT Inline
[
dt
24.03.2014 12:57
2014-03-24 14:42:05.093
];
create a another field as below
alt(timestamp(timestamp#(Date,'DD.MM.YYYY hh:mm'),'DD/MM/YYYY hh:mm:ss'),
timestamp(timestamp#(Date,'YYYY-MM-DD h:mm:ss[.fff]'),'DD/MM/YYYY hh:mm:ss')) as Date