Text / Date calculations
Hi, I seem to be going round in circles and losing the plot for something which should be really simple.
I import a file which has a text field which is a time stamp in the format yyyy/mm/dd hh:mm:ss for this particular problem all I need to do is get the date - not much of a problem other than its in yyyy/mm/dd I can't seem to flip it so that I can get the days elapsed between the timestamp and a manually entered date which is in the format dd/mm/yyyy days(f1,f2) returns a function not defined error which I guess is either because f1 is a calculated field or the wrong format. Any Suggestions Please?
-
You can split the text date into year, month and day parts and use the
date()function to convert it to a date type...let dateArr := split(YourTextDate, "/");let dateFromText := date(number(item(dateArr, 0)), number(item(dateArr, 1)), number(item(dateArr, 2))) -
Thanks getting closer when I try
let DD := split('esd', "-");
let DD2 := date(number(item(DD, 0)), number(item(DD, 1)), number(item(DD, 2)));
DD2the value in ESD is 2020-09-24T23:00:00+00:00 however DD2 returns 31/08/2020, so I tried to break down a bit more
let dateArr := split('ESD', "-");
let Year := number(item(dateArr, 0));
let Month := number(item(dateArr, 1));
let Date := number(item(dateArr, 2));
Date + "/" + Month + "/" + YearReturns 0/9/2020
let dateArr := split('ESD', "-");
let Year := number(item(dateArr, 0));
let Month := number(item(dateArr, 1));
let Date := number(item(dateArr, 2));
date(Date + "/" + Month + "/" + Year)Returns 01/01/1970
Also tried another idea whilst not 100% or foolproof a it would apply to around 70% of the imported records, setting a trigger on update to set the field to AESD:=today() but this randomly seems to set either todays date or yesterdays (oviously putting in a check to only update if blank)
-
Maybe:
let dateArr := split(substr(esd, 0, 10), "-");date(number(item(dateArr, 0)), number(item(dateArr, 1)), number(item(dateArr, 2))) -
I apologize. I focused on the date and forgot about the time stamp. In addition to Alain's solution you have the option of using regex...
let dateArr := split(extractx(esd, "\d+-\d+-\d+"), "-") -
Many thanks
let dateArr := split(substr(esd, 0, 10), "-");
date(number(item(dateArr, 0)), number(item(dateArr, 1)), number(item(dateArr, 2)))Worked perfectly
-
It works, but don't hesitate to use the regex solution instead. It should be more robust, if for example a leading space appears from nowhere, of if a day or month lacks the leading zero.
-
I will, that patricular field is always padded out and never has any spaces, however I have other projects where it will come in useful as they are padded with spaces, slowly but surely starting to get my head around extracting data from badly formatted data.