Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
fallenangel6
Creator
Creator

Date Conversion

 

I have dates in 5 fields in the below format.

Apr 1 2019 12:00AM

I need to convert this into 1/04/2019 . how can i do this?

 

Labels (1)
1 Solution

Accepted Solutions
fallenangel6
Creator
Creator
Author

okay, i did it the long way.

makedate(right(left(HOLD_DATE,11),4),match(left(left(HOLD_DATE,11),3),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),SubField(left(HOLD_DATE,11),' ',3)) as HOLD_DATE

Not sure if this is efficient but if any of you have a better solution, then please do suggest.

View solution in original post

6 Replies
Arthur_Fong
Partner
Partner

Try this:

=DATE(date#(left('Apr 1 2019 12:00AM',index('Apr 1 2019 12:00AM',' ',3)),'MMM DD YYYY'),'D/MM/YYYY')

Kas
Contributor
Contributor

Try this

=Date(Date#(DateField, 'MMM DD YYYY hh:mm TT'), 'D/MM/YYYY')

or

=Date(Date#('Apr 1 2019 12:00 AM', 'MMM DD YYYY hh:mm TT'), 'D/MM/YYYY')

fallenangel6
Creator
Creator
Author

Date Apr 1 2019 12:00AM has two blank space between Apr and 1

Apr<space><space>1 2019 12:00AM

fallenangel6
Creator
Creator
Author

okay, i did it the long way.

makedate(right(left(HOLD_DATE,11),4),match(left(left(HOLD_DATE,11),3),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),SubField(left(HOLD_DATE,11),' ',3)) as HOLD_DATE

Not sure if this is efficient but if any of you have a better solution, then please do suggest.

khan_imran
Creator II
Creator II

Try this out:

 

Test:
LOAD * INLINE [
Date
Apr 11 2019 12:00AM
];

A1:


LOAD *,
mid(Date,9,4) as Year,
Left(Date,3) as Month,
mid(Date,6,2) as Dated,
makedate(mid(Date,9,4),match(Left(Date,3),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),mid(Date,6,1)) as Date1
Resident Test;

Shayynee
Contributor
Contributor

Hi

This works for me:

Date(Date#(Trim(Left(HOLD_DATE,11)),'MMM D YYYY'),'D/MM/YYYY') as HOLD_DATE