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

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

suraj_qlikdev — Wed, 25 Nov 2020 16:16:04 GMT

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!

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

dplr-rn — Wed, 12 Sep 2018 15:55:29 GMT

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

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

YoussefBelloum — Wed, 12 Sep 2018 16:14:50 GMT

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'))

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

suraj_qlikdev — Wed, 12 Sep 2018 17:47:11 GMT

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

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

dplr-rn — Wed, 12 Sep 2018 17:48:39 GMT

great. hope it works out

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

suraj_qlikdev — Wed, 12 Sep 2018 17:53:18 GMT

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.

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

vishsaggi — Wed, 12 Sep 2018 18:58:21 GMT

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')

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

YoussefBelloum — Wed, 12 Sep 2018 19:32:38 GMT

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

good luck

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

suraj_qlikdev — Wed, 12 Sep 2018 19:36:47 GMT

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.

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

vishsaggi — Wed, 12 Sep 2018 20:43:56 GMT

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.