Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
in the process of loading my data, i found out that date column was in this format " Thu Jul 09 12:33:44 WAT 2009 " and when i tried to use the function Day on that format it didn't work !
So i was wondering if there's a format transformation function that could help me or any other solution !
Try like this
Date(Date#(SubField(Replace(Date22,'WAT ',''),' ',-1)&'-'&SubField(Replace(Date22,'WAT ',''),' ',2)&'-'&SubField(Replace(Date22,'WAT ',''),' ',3),'YYYY-MMMM-DD')) As Date23
Load
Replace(Date22,'WAT ','') as Date22,
Date(Date#(SubField(Replace(Date22,'WAT ',''),' ',-1)&'-'&SubField(Replace(Date22,'WAT ',''),' ',2)&'-'&SubField(Replace(Date22,'WAT ',''),' ',3),'YYYY-MMMM-DD')) As Date23
Inline
[Date22
Thu Jul 09 12:33:44 WAT 2009
];
Another option would be this:
Table:
load
*,
MakeDate(Year,Month,Day) as Date_Formatted;
load
Date#(SubField(replace(date_string,' ',','),',',1),'WWW') as DayName,
Month(Date#(SubField(replace(date_string,' ',','),',',2),'MMM')) as Month,
Day(Date#(SubField(replace(date_string,' ',','),',',3),'DD')) as Day,
SubField(replace(date_string,' ',','),',',4) as Time,
Year(Date#(SubField(replace(date_string,' ',','),',',6),'YYYY')) as Year;
load * Inline [
date_string
" Ter Jul 09 12:33:44 WAT 2009 "
];