
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
Thanks!
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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'))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I kicked this over to our ETL team. They are tweaking it in the source. Thanks!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
great. hope it works out

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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')

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
my bad, didn't think about it.. if you add some conditions on it, it will work
good luck

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
