Date transaformation

[Anonymous]

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 !

[sasiparupudi1]

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

];

[Thiago_Justen_]

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 "

];