Convert mixed text dates to Date

[johngouws]

Hello.

I have a need to show rates per day but have come across a stumbling block with the date outputs I am receiving.

The date reads M/D/Y but the lengths between the '/' vary. For example, there can are either 1 or 2 values for the month and the same applies to the date. The only constant is the Year: Currently I am doing the below, but I think there must be a more efficient solution.

Any ideas how I could accomplish this will be appreciated.

tmp:

Load * INLINE [

RowNo, Date, Ex_Rate

1, 9/18/2017, 14

2, 12/9/2017, 13.5] ;

NoConcatenate

tmp2:

Load

RowNo,

If(Index(Date, '/', 1) =2,

     Left(Date,1), 

     Left(Date,2) ) as Day,

If(Index(Date, '/', 1) =2,

     Mid(Date,3,2), 

     Mid(Date,4,1) ) as Month,

Right(Date,4) as Year,

Date,

Ex_Rate

Resident tmp;

Drop Table tmp;

[tresesco]

How can you be sure if 12/9 is 12th Sep or 9th Dec?

[antoniotiman · Accepted solution]

In Script

SET DateFormat='M/D//YYYY';

Regards,

Antonio

[johngouws]

If I look at RowNo 1, the 18th has to be a Date. There after I have to trust the constant format.

[vishalmanu]

Also you can try converting multiple date formats into single format using Alt function and date#.

[tresesco]

That means you are getting the format from first row. If so, what if your first row was like: 10/12/2017 ?

[johngouws]

Hello Antonia:

Sometimes the easiest solves the issue.

Thank you