topic Re: Date Conversion in QlikView

Date Conversion

fallenangel6 — Sat, 16 Nov 2024 19:28:36 GMT

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?

Re: Date Conversion

Arthur_Fong — Thu, 12 Dec 2019 06:29:55 GMT

Try this:

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

Re: Date Conversion

Kas — Thu, 12 Dec 2019 06:31:03 GMT

Try this

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

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

Re: Date Conversion

fallenangel6 — Thu, 12 Dec 2019 07:10:26 GMT

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

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

Re: Date Conversion

fallenangel6 — Thu, 12 Dec 2019 07:54:51 GMT

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.

Re: Date Conversion

khan_imran — Thu, 12 Dec 2019 08:02:31 GMT

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;

Re: Date Conversion

Shayynee — Thu, 12 Dec 2019 09:42:26 GMT

This works for me:

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