Qlik Community

Ask a Question

Deployment Framework

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

How to convert multiple date format to single date format in single field

Hi All,

I have some date field data. i need to convert all date in same format.

  

DateRequire Date
5-Jan-16 05/01/2016
24-Aug-1624/08/2016
26-Aug-1626/08/2016
Jul 15-16  201616/07/2016
31-Mar-1631/03/2016
Aug 28-29 201629/08/2016
3 Replies
MVP & Luminary
MVP & Luminary

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


talk is cheap, supply exceeds demand
Explorer
Explorer

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]

Contributor II
Contributor II

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

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ConditionalFun...

Regards