Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have some date field data. i need to convert all date in same format.
Date | Require Date |
5-Jan-16 | 05/01/2016 |
24-Aug-16 | 24/08/2016 |
26-Aug-16 | 26/08/2016 |
Jul 15-16 2016 | 16/07/2016 |
31-Mar-16 | 31/03/2016 |
Aug 28-29 2016 | 29/08/2016 |
See page 7 of this document: QlikView Date fields
For the weird dates that are not dates like Jul 15-16 2016 you'll have to use something like Date(Date#(Left(Date,4) & Right(Date,7),'MMM DD YYYY'),'DD/MM/YYYY')
Use of Alt function should do the trick. To parse a text like "Jul 15-16 2016", I would use SubField function to be safe (in case of handling a single-digit date range, such as 8-9).
Date(
Alt(
Date#([Date],'D-MMM-YY'),
Date#(
SubField([Date],' ',1)&
SubField(SubField([Date],' ',2),'-',2)&
SubField([Date],' ',3),
'MMMDDYYYY')
),'DD/MM/YYYY') as [Require Date]
Hi,
you can use this code:
Date(Alt(
Date#(TxtDate,'DD-MMM-YYYY' ),
Date#(TxtDate,'DD-MMM-YY' ),
Date#(TxtDate,'DD/MMM/YYYY' ),
Date#(TxtDate,'DD/MMM/YY' ),
Date#(TxtDate,'MMM/DD/YYYY' ),
Date#(TxtDate,'MMM/DD/YY' ),
Date#(TxtDate,'DD/MM/YYYY' ),
Date#(TxtDate,'DD-MM-YYYY' ),
Date#(TxtDate,'YYYY/MMM/DD' ))) as FixDate
Regards