topic Re: convert string to date in QlikView

convert string to date

realpixel — Mon, 18 Mar 2013 18:10:42 GMT

Hello,

I'm novice on QV.

I loaded an Excel file, I have a column named "DATE MOV" with date at format 20130314, how to convert all data content in this column to format DD/MM/YYYY

I have read some post on this subject without to find a solution understandable for me.

Re: convert string to date

swuehl — Mon, 18 Mar 2013 18:27:57 GMT

Try using date#() and date() functions with appropriate format codes (more details in the HELP):

LOAD

Date(Date#([DATE MOV],'YYYYMMDD'),'DD/MM/YYYY') as [DATE MOV],

....

FROM ...;

Re: convert string to date

Mon, 18 Mar 2013 18:30:55 GMT

Please read the below article, it is very helpful on dates. After I read the below it made my understanding lot better.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work

Re: convert string to date

Anonymous — Mon, 18 Mar 2013 18:45:53 GMT

Exactly.

Function date#() tells how to read data in a date format.

Function date() tells how to format this data.

Regards,

Michael

Re: convert string to date

realpixel — Mon, 18 Mar 2013 18:47:52 GMT

Thank you for your answer.

I have already try the link below.

If I use integer option I have a value like that : 20 130 314

If I use timestamp option I have a value like that : 04/12/57014 00:00:00

I have try the function without success maybe because not correctly palced in the script?

LOAD

Date(Date#([DATE MOV],'YYYYMMDD'),'DD/MM/YYYY') as [DATE MOV],

Re: convert string to date

realpixel — Mon, 18 Mar 2013 18:54:19 GMT

My script is like that.

LOAD [CODE PARC],

[Nø CONT],

[DATE MOV],

[Nø ALLOTISSMT],

[CODE MVT],

[SENS MVT 1/2/3],

[TAG DER MVT],

[EXP IMP TRB],

[V/P],

[TYPE ISO],

[FAMILLE VOL.TYP],

[Nø SCELLE ARMA],

[Nø SCELLE CLIENT],

[TAG RSRV 0/1],

[CODE EXPLOITANT ],

[CODE TRANS],

[NUMERO IMMATRICU LATION],

[LIEU POSITIONNEMENT],

[NUMERO],

[CODE ],

[Nø VOY],

[DATE ETA],

[DATE ETD],

[CODE ORIG.],

[CODE CHAR],

[CODE DECHA],

FROM

C:\TEST\TEST\test.XLS

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Where put Date(Date#([DATE MOV],'YYYYMMDD'),'DD/MM/YYYY') as [DATE MOV]?

Re: convert string to date

Anonymous — Mon, 18 Mar 2013 18:57:00 GMT

Put it in place whe you currently have [DATE_MOVE]:

LOAD [CODE PARC],

[Nø CONT],

Date(Date#([DATE MOV],'YYYYMMDD'),'DD/MM/YYYY') as [DATE MOV] as [DATE MOV],

[Nø ALLOTISSMT],

....

Re: convert string to date

realpixel — Mon, 18 Mar 2013 19:05:40 GMT

It works! Thank you everybody for your assistance.

Re: convert string to date

mvaugusto — Tue, 26 May 2015 16:27:47 GMT

Nice!