0

Convert

Is it  possible  to  convert a Text field into a DAte field and  with the  correct  format  if  possible  ie  DD/MM/YYYY ?

I imported the  entire  database  but  erroneously did  not  set the date field to  date!

4 replies

null
    • Mconneen
    • 4 yrs ago
    • Reported - view

    @Dr HH.. 
    Well.. you will not really "convert" the text field in place.. You can create a NEW Date field.. then write a loop to go through all the records and convert them.  

    Let us assume you have YourTextDateField  and its value is "04/04/2020"   (Apr 04, 2020).

    First.. Look up the date function .. you will find date(year, month, day) ... so you will want to "split" your text string as 
    let fldArray := split(YourTextDateField, "/");

    That creates an item array .... ["04", "04", "2020"]

    You can then create a new date field by 

    let YourNewDateField := date(number(item(fldArray, 2)), number(item(fldArray, 1)), number(item(fldArray, 0)))

     

    Disclaimer.. I did not try the code.. so hopefully no type-os.. 

    • Dr_HH
    • 4 yrs ago
    • Reported - view

    I dont  mind  splitting the  function 

    1  I have no hard  syntax  programming  knowledge (total no knowledge)

    2 I have attempted  copying a  formula on the forum to do a  mass repalce  but the the date got  copied  incorrectly

    • Dr_HH
    • 4 yrs ago
    • Reported - view

    Maybe just  copy the characters(numbers ( from the  Text field to a new  date field) then  f,ormat  it

    • Dr_HH
    • 4 yrs ago
    • Reported - view

    mAYBE THIS  CODE  BUT the   numbers corrected allocated   from this text  date of 1975/02/20    to  a Date ieold  1975/02/20 then  formated to DD/MM/YYYY ?

    let firstIndex := index(TextDate, "/");
    let secondIndex := if substr(TextDate, firstIndex + 2, 1) = "/" then
    firstIndex + 2
    else
    firstIndex + 3
    end;
    date(number(substr(TextDate, secondIndex + 1, 4)), number(substr(TextDate, 0, firstIndex)), number(substr(TextDate, firstIndex + 1, secondIndex - firstIndex - 1)))

Login to reply

Content aside

  • 4 yrs agoLast active
  • 4Replies
  • 1106Views