Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Creator
Creator

Re: Date Conversion

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
Highlighted
Partner
Partner

Re: Date Conversion

Try this:

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

Highlighted
Contributor
Contributor

Re: Date Conversion

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')

Highlighted
Creator
Creator

Re: Date Conversion

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

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

Highlighted
Creator
Creator

Re: Date Conversion

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

Highlighted
Creator
Creator

Re: Date Conversion

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;

Highlighted
Contributor
Contributor

Re: Date Conversion

Hi

This works for me:

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