3 Replies Latest reply: Oct 30, 2012 4:17 AM by Markus Bauernschmitt RSS

    Date of Birth Incorrect

    Gerhard Laubscher



      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,



        • Re: Date of Birth Incorrect
          Stefan Wühl

          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.



          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...

          • Date of Birth Incorrect
            Celambarasan Adhimulam


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