Handling of different date formats

[qlikviewwizard]

Hi All,

I have data as shown below

Date Format	Date	Quarter	Month
M/D/YYYY	8/5/2013	Q3	Aug
M/D/YYYY	10/7/2013	Q4	Oct
DD/MM/YYYY	15/04/2013	Q	-
DD/MM/YYYY	18/07/2013	Q	-
DD/MM/YYYY	20/05/2013	Q	-
DD/MM/YYYY	21/06/2013	Q	-
DD/MM/YYYY	22/01/2013	Q	-
DD/MM/YYYY	22/03/2013	Q	-
DD/MM/YYYY	24/07/2013	Q	-
DD/MM/YYYY	24/09/2013	Q	-
DD/MM/YYYY	26/06/2013	Q	-
DD/MM/YYYY	27/03/2013	Q	-
DD/MM/YYYY	29/07/2013	Q	-
DD/MM/YYYY	29/10/2013	Q	-

When I derive Quarter, it is showing Q for DD/MM/YYYY. How to bring all the data into uniform format and eliminate Q.

Thanks in advance.

[ziadm]

Hi

use the Alt function to return the valid date

alt( date#( MyDate, 'YYYY/MM/DD' ),

date#( MyDate, 'MyDate MM/DD/YYYY' ),

date#( MyDate, 'MM/DD/YY' ),

'Not valid date' )

Load

LOAD ,

alt( date#( MyDate, 'YYYY/MM/DD' ),

date#( MyDate, 'MyDate MM/DD/YYYY' ),

date#( MyDate, 'MM/DD/YY' ),

'Not valid date' )  as MyNewDate

FROM DB

You may extend it to as many formats as you think you could be loading 'MM-DD-YYYY', YYYY-MM-DD'

If this works please tag as resolved

Regards

Ziad

[srchilukoori · Accepted solution]

Add the following statement to the default environment variables on the qvw.

SET DateFormat='DD/MM/YYYY';