Solved: Date convert - without Alt function - Qlik Community

qlikviewwizard · ‎2017-05-31

How to convert the Date column to DATE format.

I tried the below code but format function Date# is not working for second record.

Please advise.

Data:

load * ,DATE(DATE#(Date#(Date,'YYYY/DD/MM'),'YYYY/MM/DD')) AS NEW_DATE;

load * inline [

Sno,Date

1,2010/31/01

2,2011/02/28

];

tresesco · ‎2017-05-31

I guess it would not take much of your time - to check if that works or not. Just try yourself like:

Data:

load * ,DATE(If( Isnum(Date#(Date,'YYYY/DD/MM')),Date#(Date,'YYYY/DD/MM'),DATE#(Date,'YYYY/MM/DD'))) AS NEW_DATE;

load * inline [

Sno,Date

1,2010/31/01

2,2011/02/28

];

Did it work? Please let know.

View solution in original post

prieper · ‎2017-05-31

What do you want to achieve?

What would be the criteria to differentiate, whether the record is in format YYYY/DD/MM or in format YYYY/MM/DD?

The only chance (but not failproof) would be to identify a Daynumber > 12, but this will not help with entries like 2017/03/04.

Anil_Babu_Samineni · ‎2017-05-31

May be this?

Data:

LOAD Sno, Date, Alt(Date(Date#(Date,'YYYY/MM/DD'),'YYYY/MM/DD'), Date(Date#(Date,'YYYY/DD/MM'),'YYYY/MM/DD')) as NEW_DATE;

load * inline [

Sno,Date

1,2010/31/01

2,2011/02/28

];

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

qlikviewwizard · ‎2017-05-31

2010/31/01 and 2011/02/28 are two dates with different formats. I want to correct this data issue without using Alt function to valid date format.

Hope, I explained the issue clearly.

qlikviewwizard · ‎2017-05-31

Hi loveisfail‌

I want to check the solution without using Alt function.

prieper · ‎2017-05-31

.... and how do you think a differentiation can be made?

Anil_Babu_Samineni · ‎2017-05-31

I don't think so Can i know the reason to avoid the Alt() function. We can achieve by help of two inner joins. But, I don't recommend to use that. How we know till how many dates are there. I would always go for Alt() only this case

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

qlikviewwizard · ‎2017-05-31

I agree with you

prieper · ‎2017-05-31

You may think about a solution like

MAKEDATE(LEFT(MyDate, 4), IF(MID(MyDate, 6,2) > 12, RIGHT(MyDate, 2), MID(MyDate, 6, 2), IF(MID(MyDate, 6, 2) > 12, MID(MyDate, 6, 2), RIGHT(MyDate, 2)) AS NewDate

However, the question remains, what makes you sure to read entries like 2017/03/04 in the correct way?

hemanthaanichet · ‎2017-05-31

Hi Arjun,

I think using alt is the finest way of converting diff date formats into single format

Data:

LOAD Sno, Date, Date(Alt(Date#(Date,'YYYY/MM/DD'), Date#(Date,'YYYY/DD/MM'))) as NEW_DATE;

load * inline [

Sno,Date

1,2010/31/01

2,2011/02/28

];