Bad date format

[ced_foning]

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. 

[agigliotti]

in the script let's try as below:

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

[StarinieriG · Accepted solution]

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

[ced_foning]

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 ? 

[ced_foning · Accepted solution]

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 

[StarinieriG]

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')