Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
realpixel
Contributor III
Contributor III

convert string to date

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.

1 Solution

Accepted Solutions
Anonymous
Not applicable

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],

....

View solution in original post

8 Replies
swuehl
MVP
MVP

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 ...;

Not applicable

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

Anonymous
Not applicable

Exactly.

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

Function date() tells how to format this data.

Regards,

Michael

realpixel
Contributor III
Contributor III
Author

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],

realpixel
Contributor III
Contributor III
Author

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]?

Anonymous
Not applicable

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],

....

realpixel
Contributor III
Contributor III
Author

It works! Thank you everybody for your assistance.

mvaugusto
Creator
Creator

Nice!