Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Bad date format

Hello, 

Here is a set of data from SQL server. 

I have a problem with my date format. In my field, i have different date format. Some of the dates starts on the left, others on the right. I wish to have the date in the following format : DD/MM/YYYY. 

date.PNG

Labels (2)
2 Solutions

Accepted Solutions
Highlighted
Partner
Partner

Hi,

 

try to convert it in this way (script): 

Date(If(IsNull(Date(opti_Date_MESC_ARCEP_PM)),Date#(opti_Date_MESC_ARCEP_PM,'YYYY/MM/DD'),opti_Date_MESC_ARCEP_PM),'DD/MM/YYYY') as opti_Date_MESC_ARCEP_PM

View solution in original post

Highlighted
Contributor III
Contributor III

For the dataset above, i did the following : 

 

substringcount(opti_Date_MESC_ARCEP_PM,'/')=0 and len(opti_Date_MESC_ARCEP_PM)=8,
makedate(left(opti_Date_MESC_ARCEP_PM,4),mid(opti_Date_MESC_ARCEP_PM,5,2),right(opti_Date_MESC_ARCEP_PM,2)). 

 

So the final solution is : 

if(
substringcount(opti_Date_MESC_ARCEP_PM,'/')=0 and len(opti_Date_MESC_ARCEP_PM)=8,
makedate(left(opti_Date_MESC_ARCEP_PM,4),mid(opti_Date_MESC_ARCEP_PM,5,2),right(opti_Date_MESC_ARCEP_PM,2)),

Date(If(IsNull(Date(opti_Date_MESC_ARCEP_PM)),Date#(opti_Date_MESC_ARCEP_PM,'YYYY/MM/DD'),opti_Date_MESC_ARCEP_PM),'DD/MM/YYYY')
) as Date2 

View solution in original post

5 Replies
Highlighted
Partner
Partner

in the script let's try as below:

load
...
Date( opti_Date_MESC_ARCEP_PM, 'DD/MM/YYYY') as opti_Date_MESC_ARCEP_PM

Highlighted
Partner
Partner

Hi,

 

try to convert it in this way (script): 

Date(If(IsNull(Date(opti_Date_MESC_ARCEP_PM)),Date#(opti_Date_MESC_ARCEP_PM,'YYYY/MM/DD'),opti_Date_MESC_ARCEP_PM),'DD/MM/YYYY') as opti_Date_MESC_ARCEP_PM

View solution in original post

Highlighted
Contributor III
Contributor III

Thanks for your reply. 

Its working for some, and not for others. 

Here is a set of data. How can i have a good set from your script above ? 

 

Capture.PNG

Highlighted
Contributor III
Contributor III

For the dataset above, i did the following : 

 

substringcount(opti_Date_MESC_ARCEP_PM,'/')=0 and len(opti_Date_MESC_ARCEP_PM)=8,
makedate(left(opti_Date_MESC_ARCEP_PM,4),mid(opti_Date_MESC_ARCEP_PM,5,2),right(opti_Date_MESC_ARCEP_PM,2)). 

 

So the final solution is : 

if(
substringcount(opti_Date_MESC_ARCEP_PM,'/')=0 and len(opti_Date_MESC_ARCEP_PM)=8,
makedate(left(opti_Date_MESC_ARCEP_PM,4),mid(opti_Date_MESC_ARCEP_PM,5,2),right(opti_Date_MESC_ARCEP_PM,2)),

Date(If(IsNull(Date(opti_Date_MESC_ARCEP_PM)),Date#(opti_Date_MESC_ARCEP_PM,'YYYY/MM/DD'),opti_Date_MESC_ARCEP_PM),'DD/MM/YYYY')
) as Date2 

View solution in original post

Highlighted
Partner
Partner

Hi,

you could add this part to cover those data format too:

Date(
If(IsNull(Date(opti_Date_MESC_ARCEP_PM)),
Date#(opti_Date_MESC_ARCEP_PM,'YYYY/MM/DD'),
If(Len(opti_Date_MESC_ARCEP_PM)=8,Date#(opti_Date_MESC_ARCEP_PM,'YYYYMMDD'),
opti_Date_MESC_ARCEP_PM)),'DD/MM/YYYY')