Date format DD-MMM-YY to DD-MM-YYYY issue

[suraj_qlikdev]

Hi All,

I am trying to convert the date format from DD-MMM-YY to DD-MM-YYYY using the below expression.

=Date(Date#(BIRTHDATE,'DD-MMM-YY'),'DD-MM-YYYY')

Date type of the field from the source is Varchar.

I am see issues with years <= 66. Example -  YY 66 gets formatted to 2066 instead of 1966. However, >= 67 YY formats without any issues. Can someone please help me with this issue?

Thanks!

[dplr-rn · Accepted solution]

Issue is the source column being a varchar. IMHO it leads qlik to guess. Which leads to the results you mentioned

You might have to get down and dirty while loading the data

[YoussefBelloum]

Hi,

on the script, you can try this:

table:

load

.

.

if(Date(Date#(BIRTHDATE,'DD-MMM-YY'),'YY') <= 66, Date(Date#(BIRTHDATE,'DD-MMM-),'YY')&'-19'&Date(Date#(BIRTHDATE,'DD-MMM-YY'),'YY'), Date(Date#(BIRTHDATE,'DD-MMM-YY'),'DD-MM-YYYY'))

[suraj_qlikdev]

I kicked this over to our ETL team. They are tweaking it in the source. Thanks! 

[dplr-rn]

great. hope it works out

[suraj_qlikdev]

Thanks. I tried this. It works partially meaning, a few instances where BIRTHDATE is 01-APR-2011, YY = 11 which is <= 66 and  adds a prefix 19 formats it as 1966 which is incorrect.

[vishsaggi]

Ref taken from Colin albert's (Thanks @Albert for your expression) expr and tweaked. Check here: Convert 6 digit number to date

may be you can try like below: however you need to give a range for the year 19 to consider, I have taken the years between 30 and 67 will fall in 19 and anything apart will have 20. Hope this is ok?

= Date(Date#(Left('06-APR-66',7) &

          if( Mid('06-APR-66',8,2) > 30 AND Mid('06-APR-66',8,2) < 67, '19' & Mid('06-APR-66',8,2), '20' & Mid('06-APR-66',8,2)), 'DD-MMM-YYYY'), 'DD-MM-YYYY')

[YoussefBelloum]

my bad, didn't think about it.. if you add some conditions on it, it will work

good luck

[suraj_qlikdev]

Awesome! Tweaked your expression just a little bit and this solves my problem.  

= Date(Date#(Left(BIRTHDATE,7) & if( Mid(BIRTHDATE,8,2) > RIGHT(YEAR(TODAY( )),2) AND Mid(BIRTHDATE,8,2) <= 99, '19' & Mid(BIRTHDATE,8,2), '20' & Mid(BIRTHDATE,8,2)), 'DD-MMM-YYYY'), 'DD-MM-YYYY')

vishsaggi‌ - Thank you.

[vishsaggi]

Glad it worked for you. However, i still suggest you to touch base with backend team if they can get the year in 4digits which makes life simpler.