Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

suraj_qlikdev
New 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
Partner
Partner

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

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

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

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

YoussefBelloum
Esteemed Contributor

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

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

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

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

Partner
Partner

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

great. hope it works out

suraj_qlikdev
New Contributor III

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

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

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

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
Esteemed Contributor

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

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

good luck

Highlighted
suraj_qlikdev
New Contributor III

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

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

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

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.