0

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?

7 replies

null
    • Sean
    • 4 yrs ago
    • Reported - view

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

    • support.1
    • 4 yrs ago
    • Reported - view

    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)));
    DD2

     

    the 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 + "/" + Year

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

    • Alain_Fontaine
    • 4 yrs ago
    • Reported - view

    Maybe:

    let dateArr := split(substr(esd, 0, 10), "-");
    date(number(item(dateArr, 0)), number(item(dateArr, 1)), number(item(dateArr, 2)))

    • Sean
    • 4 yrs ago
    • Reported - view

    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+"), "-")

    • support.1
    • 4 yrs ago
    • Reported - view

    Many thanks 

    let dateArr := split(substr(esd, 0, 10), "-");
    date(number(item(dateArr, 0)), number(item(dateArr, 1)), number(item(dateArr, 2)))

     

    Worked perfectly

    • Alain_Fontaine
    • 4 yrs ago
    • Reported - view

    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.

    • support.1
    • 4 yrs ago
    • Reported - view

    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.

Login to reply

Content aside

Categories

View all topics
  • 4 yrs agoLast active
  • 7Replies
  • 856Views