LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dglavastorm

Hi everyone ,

I have a date/time field that imports in Unicode that I need to either split into Date and Time columns or have as a correct DateTime format column. The issue is that the data is all different lengths as it doesn't populate zeroes to make all the date/times the same length.

eg.
2014-11-5 9:44:19
2014-11-5 8:6:7
2014-10-30 12:25:57

I can seperate the date and convert as this formula seems to have no issue converting single digit dates to double
Date1 = left('Input',strFindI('Input'," "))
Date2 = date(Date1,"CCYY-MM-D")
Dates above correctly become
2014-11-05
2014-11-05
2014-10-30

But I cannot get the same to work with Time.

This formula extracts the time exactly regardless of the number of characters but leaves it as Unicode -
Time1 = right('Response Date in Melbourne Time',(strlen('Input')-strFindI('Input'," ")))

Nothing I've tried will convert this Unicode to Time, either individually or as a full DateTime
eg.
Time2 = time(Time1,"HH:MM:SS returns " cannot parse value ' 9:44:19' with format 'HH:MM:SS'". This the same no matter what variation of "HH:MM:SS" I try (eg. H:MM:SS)

Time2 = Time1.Time() returns "parameter must be either a datetime or a numeric type".

DateTime(Date2,Time1) returns "The operator requires a date value as parameter 1. The parameter value passed to it is a unicode."


The Data Analyzer node on the original data just converts it to a string so this doesn't work either.

I even tried replacing the ":" with "." to convert it to a double or other numerical type ( I don't really care about seconds) but again I'm stuck given the multiple "." and the different lengths.

Any help greatly appreciated as I'm completely out of ideas.

Cheers,

Dave

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

So H:MM:SS should have worked, but there's a space in front of the 9. You could have performed a left trim (LTRIM) on it.

In the attached example, I used the SPLIT function to split up the date and time. These should return a list of two elements - date and time. I then use DATE and TIME to parse the two elements.
Attachments:
DateTime.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dglavastorm

Hi Ryeh. Trim functions didn't help ( I tried Ltrim and normal trim and it still returned the same "couldn't parse" error) but the Split function worked perfectly, even more so as I now have all three columns - Datetime, Date and Time

Many thanks again :-)