topic Re: conversion of dates in QlikView

conversion of dates

shree909 — Mon, 20 May 2013 16:12:56 GMT

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

Re: conversion of dates

rustyfishbones — Mon, 20 May 2013 16:34:15 GMT

Try

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

Re: conversion of dates

rbecher — Mon, 20 May 2013 16:35:28 GMT

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

Re: conversion of dates

shree909 — Mon, 20 May 2013 16:36:40 GMT

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