Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
suraj_qlikdev
Contributor III
Contributor III

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

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?


DOB issue - 1.png


DOB 2.png

DOB issue - 3.png

Thanks!

1 Solution

Accepted Solutions
dplr-rn
Partner - Master III
Partner - Master III

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

View solution in original post

9 Replies
dplr-rn
Partner - Master III
Partner - Master III

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

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
Contributor III
Contributor III
Author

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

dplr-rn
Partner - Master III
Partner - Master III

great. hope it works out

suraj_qlikdev
Contributor III
Contributor III
Author

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
Champion III
Champion III

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

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

good luck

suraj_qlikdev
Contributor III
Contributor III
Author

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
Champion III
Champion III

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.