topic Re: How to Convert 7 or 8 character numeric string to date in QlikView

How to Convert 7 or 8 character numeric string to date

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

Hello.

Hopefully the Qlik Community can assist me. I am attempting to load from a *.csv file a series of dates formatted as a text string. The text string varies in length between either a 7 or 8 character string which is either DMMYYYY (7 character string) or DDMMYYYY (8 character string).

In the load statement I would like to have these text string converted to the date format DD-MM-YYYY.

For example my data looks like:

Name	Date_of_birth (as text string)	Wanted Date Format
John Smith	1101983	01/01/1983
Dee White	27121975	27/12/1975
Marg All	16062001	16/06/2001
Win Cooper	9102012	09/10/2012
Sam Mac	7072007	07/07/2007
Wanda McT	5091962	05/09/1962
Steve Rudd	21021964	21/02/1964
Tim Jurd	18011938	18/01/1938

How can I accomplish this?

So far I have tried combinations of several statements including:

IF (LEN(Date_of_birth]) = 7, '0' & Date_of_birth, Date_of_birth) as Date_of birth, this worked to make the 7 character string to an 8 character string

Also I tried converting to date using

Date(Date# ([Date of birth],'DDMMYYYY'), 'DD-MM-YYYY') as [Date of birth], but this gives me incorrect dates, for example 7 character string 2111934 gives me 21-11-0934, where I am expecting 02/11/1934. While 8 character string 15121991 is interpreted correctly as 15-12-1991.

Re: How to Convert 7 or 8 character numeric string to date

effinty2112 — Wed, 30 Aug 2017 15:36:43 GMT

Hi Matthew,

Try

Date(Date#(Right('0' &[Date_of_birth (as text string)],8),'DDMMYYYY'),'DD/MM/YYYY')

in a chart or in script as you prefer.

giving

Date_of_birth (as text string)	Date(Date#(Right('0' &[Date_of_birth (as text string)],8),'DDMMYYYY'),'DD/MM/YYYY')
1101983	01/10/1983
5091962	05/09/1962
7072007	07/07/2007
9102012	09/10/2012
16062001	16/06/2001
18011938	18/01/1938
21021964	21/02/1964
27121975	27/12/1975

Cheers

Andrew

Re: How to Convert 7 or 8 character numeric string to date

Anonymous — Wed, 30 Aug 2017 23:02:00 GMT

Thank you Andrew, your solution works perfectly.

For those that may be reading along I did remove the text " (as text string)" to get it to work.

I wouldn't of been able to come up with that one myself. This will help me read correctly the 10 or other date fields in my data set.