Date Format Conversion

[keerthika]

Hi All,

     Can any one say how to convert '4th june 2018' to '04june2018', need to convert string to date,

     I'm having many formats , like below

    January 1st 2018
February 1st 2018
April 1st 2018
July 1st 2018
11/1/2018
10/1/2018
2018
2018
October 1st 2018
October 1st 2018
November 1st 2018
12/1/2018
1/1/2019
1/1/2019
February 8th 2018
February 8th 2018
April 1st 2018
October 5th 2018
October 5th 2018
November 1st 2018
November 1st 2018
February 6th 2018

Thanks,

Keke

[tresesco]

Use alt() in combination with date#()to convert the string into unified date format, like shown here:

Multiple-Dates-Using-Alt

[keerthika]

Hi,
Iam using qlikview , does this work?

[keerthika]

For this format its not working (3rd june 2018)

[albert_guito]

Hi,
If you want to convert '4th june 2018' to '04june2018' you can try with:

If(Len(SubField(PurgeChar([YourField],'th'),' ',1))=1,0&Left([YourField],1),Left([YourField],2)&SubField([YourField],' ',2)&Subfield([YourField],' ',3)

Also you can add to PurgeChar function the rest for ordinals letters ... st, nd, etc.

Ag+

[jonathandienst]

Or try this

Date(
    If(WildMatch(DateField, '*st *', '*nd *', '*rd *','*th *'),
        Date#(
            KeepChar(SubField(DateField, ' ', 2), '0123456789') & '-' & 
            SubField(DateField, ' ', 1) & '-' & 
            SubField(DateField, ' ', 3), 'D-MMMM-YYYY),
        Alt(
            Date#(DateField, 'YYYY'),
            Date#(DateField, 'DD/MM/YYYY')
        )
    )
) as DateValue