Date of Birth Incorrect

[gerhardl]

Hi,

We have an extract with a [Date of Birth] field. The extract has a funny date format - DD-MMM-YY (e.g. 09-MAR-85).

In my script I change all my date formats like this:

DATE(DATE#( [Date of birth], 'DD-MMM-YY')) as [Date of birth],

What I noticed is that for certain records QlikView then assumes the wrong century. So for certain accounts the date of birth will then be correct - 1985/03/09.

For other accounts the date of birth will be 2085/03/09.

Any idea how I can fix this?

Thanks in advance,

Gerhard.

[swuehl]

Parsing the dates in using date# with only two digit year number is just not fully determined. No idea how QlikView determines the right century, I think that's wild guessing. In my script, it changes the century 1962, so for years <62,  it assumes 21 century, then 20th century. Maybe 50 years back in time from now? no idea.

To fix it, I think you need to add the year to your [Date of birth] using string operations, then parse the full four digit year in.

Like

Date(date#(left(DoB,7)&'20'&right(DoB,2),'DD-MMM-YYYY') as DoB,

to use only 2000 to 2099 DoB.

No idea how you should determine the correct century, that's up to you...

[CELAMBARASAN]

Hi,

     Can you tell me whats your date of birth year range?

Celambarasan

[#4]

Hello,

my Workarround:

if( Date(Date#(@211:216,'DDMMYY')) < AddYears(today(), -15),

     Date(Date#(@211:216,'DDMMYY')),

     AddYears(Date(Date#(@211:216,'DDMMYY')), -100)

) as [Day of birth]