topic Re: convert to date in QlikView

convert to date

Anonymous — Fri, 05 Jan 2018 18:12:44 GMT

How to I convert to MM/DD/YYYY format for the date which Is in "Fri Sep 08 12:40:59 PDT 2017" format?

Thanks

jcampbell474 — Fri, 05 Jan 2018 18:39:52 GMT

In your example, this expression might work:

=Date(MakeDate(Mid(Date1,Len(Date1)-3,5),Num(Month(Date#(Mid(Date1,5,3), 'MMMM'))),Mid(Date1,9,2)),'MM/DD/YYYY')

Alternatively, you can also make some changes in the load script. Depends on your application.

(Date1 = Your date field)

m_woolf — Fri, 05 Jan 2018 18:41:45 GMT

You can convert the MonthAbv to a number using a Mapping table:

MapMonthAbv:

Mapping load * inline [

MonthAbv,MonthNo

Jan,1

Feb,2

Mar,3

];

Then you can parse the current field using substring:

ApplyMap('MapMonthAbv',substring(YourField,' ',2)) as MonthNo,

substring(YourField,' ',3) as Day,

substring(YourField,' ',6) as Year,

Finally:

date(makedate(Year,MonthNo,Day),'MM/DD/YYYY') as MyDate,