conversion of dates

[shree909]

Hi  i  have a date field as  Date  as

Apr 24 2013 12:00:00:000AM

Feb  1 2013 12:00:00:000AM

May  6 2013 12:00:00:000AM

May  7 2013 12:00:00:000AM

May  8 2013 12:00:00:000AM

May  9 2013 12:00:00:000AM

May 10 2013 12:00:00:000AM

May 13 2013 12:00:00:000AM

May 14 2013 12:00:00:000AM

May 15 2013 12:00:00:000AM

   so how to convert it into the 'MM/DD/YYYY' format or nummerical value..

[rustyfishbones]

Try

DATE (DATE#(Date,'MMM DD YYYY'),'MM/DD/YYYY') AS Date

[rbecher · Accepted solution]

Hi,

this is one solutions:

raw_data:
LOAD * INLINE [
    raw_date
    Apr 24 2013 12:00:00:000AM   
    Feb  1 2013 12:00:00:000AM
    May  6 2013 12:00:00:000AM
    May  7 2013 12:00:00:000AM
    May  8 2013 12:00:00:000AM    
    May  9 2013 12:00:00:000AM    
    May 10 2013 12:00:00:000AM    
    May 13 2013 12:00:00:000AM
    May 14 2013 12:00:00:000AM
    May 15 2013 12:00:00:000AM
];
result:
LOAD 
date(date#(left(replace(raw_date, '  ', ' '), index(replace(raw_date, '  ', ' '), ' ', 3) -1), 'MMM DD YYYY'), 'MM/DD/YYYY') as the_date
resident raw_data;

- Ralf

[shree909]

Hi can u find the qvw file as attached..

I tried  the above format but its not working .

i have taken some data and loaded as a inline..