Date formatting problem

[#1]

I got a field (Bdate) which consists of a birthdate in a textformat like this 050453.

I´d like to load the field formatting it to a real date and looking like this 05-04-1953 so I can make a script that calculates the age

AGE(today(),Bdate) as Personage

What do I do?

[alexandros17]

You need at first to distinguish years before 2000:

so you may use this logic:

MakeDate(If(Right(Bdate,2) > Right(Year(Today()),2), 1900, 2000)+Right(Bdate, 2),Mid(Bdate,3,2),Left(Bdate,2) )

[manojkulkarni]

Birth date string is constant, then you can use string functions to create date field. But the problem is with year part of the string. If your data is starting from specific year then you can head with assumptions.

[CELAMBARASAN]

Use the below expression to create the field and make use of it for age calculation

AddYear(Date(Date#(Bdate, 'DDMMYY')), IF(Right(Bdate, 2) > Right(Year(Today()), 2), -100, 0)) AS BirthDateField

[amit_saini]

Hi Please use this:

MakeDate(Year(Today()),Month(DATE(DATE#( left(DOB,11), 'DD-MMM-YY')) ),Day(DATE(DATE#( left(DOB,11), 'DD-MMM-YY')) )) as Birth_date

Thanks,

AS

[MK_QSL]

=Age(Today(),Date(Date#(Bdate,'DDMMYY')))

[Anonymous]

Hi Soren,

Try this Date(Bdate,'DD-MM-YYYY') It will give the full date formate

after that u can find the age as - AGE(Today(),Date(Bdate,'DD-MM-YYYY'))

Hope this would be help u ...

[Anonymous]

There is no absolutely reliable way to get the year right (remember Y2K?).  022114 could be February 21, 2014 or February 21, 1914.  That means you have to use some rules, for example if YY<"current year", use 20YY, otherwise use 19YY.  You'll certainly have problems if the birth date is in 1914 or earlier.

BTW, Celambarasan Adhimulam is using this rule, but the rule could be different.

[hic]

1. QlikView interprets (YY=) "64" as 1964 but "63" as 2063. Try the following code and you'll see:

     Load *, Date(Date#(OriginalDate,'YYMMDD'),'YYYY-MM-DD') as Date;

     Load Num(RecNo()-1,'00') & '0331'  as OriginalDate Autogenerate 100;

So if you know that "53" means 1953, you will need to code this in your formula, with e.g. an If()-function.

2. There is usually no purpose in using the Date()-function if you wrap it in another function. For example, the Date()-function serves no purpose in

     Age(Today(),Date(Date#(Bdate,'DDMMYY')))

Better to write

     Age(Today(),Date#(Bdate,'DDMMYY'))

HIC

[Anonymous]

Try this

If(Right(BDate,2) >= 30,Date(MakeDate('19'&Right(BDate,2),Left(BDate,2),Mid(BDate,3,2)),'MM-DD-YYYY'),Date(MakeDate('20'&Right(BDate,2),Left(BDate,2),Mid(BDate,3,2)),'MM-DD-YYYY')) as BirthDate