Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
shree909
Partner - Specialist II
Partner - Specialist II

conversion of dates

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..

1 Solution

Accepted Solutions
rbecher
MVP
MVP

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

Astrato.io Head of R&D

View solution in original post

3 Replies
rustyfishbones
Master II
Master II

Try

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

rbecher
MVP
MVP

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

Astrato.io Head of R&D
shree909
Partner - Specialist II
Partner - Specialist II
Author

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..