topic Re: Date Format Conversion in QlikView

Date Format Conversion

keerthika — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Date Format Conversion

tresB — Thu, 13 Dec 2018 08:07:48 GMT

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

Multiple-Dates-Using-Alt

Re: Date Format Conversion

keerthika — Thu, 13 Dec 2018 08:13:42 GMT

Hi,
Iam using qlikview , does this work?

Re: Date Format Conversion

keerthika — Thu, 13 Dec 2018 08:16:37 GMT

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

Re: Date Format Conversion

albert_guito — Thu, 13 Dec 2018 08:35:47 GMT

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+

Re: Date Format Conversion

jonathandienst — Thu, 13 Dec 2018 09:11:38 GMT

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