Issue with importing date field with time value from text file

ijaiya007

Forums

Hi,
I am trying to import a delimited text file and i keep getting 'error' value for the date field after import.  The date field has time values (e.g. 2019-04-6 24:56:44.00) and i use the YYYYMMDD.
How can I go about this as if I change the field type to character, it comes out fine but i would not be able to perform date related analysis on that particular.

klmi Wed, 08/12/2020 - 02:17

1) Import the date/time field with field type character into IDEA.
2) Add a new date field and use IDEA's integrated @functions to extract the date information from the character field.
To split date and time f.e. use @split(ALPHANUM;"";" ";1)  as equation. Basically it would be possible to convert the splitted date (type character) with @ctod(TEST;"YYYY-MM-DD") to a date field. However I see the problem with the day values in your example "2019-04-6 24:56:44.00" - the date is not "YYYY-MM-DD" but "YYYY-MM-D". If so you need one more step to handle that: @If(@len(TEST)==10;@Ctod(TEST;"YYYY-MM-DD");@Ctod(TEST;"YYYY-MM-D")).

ijaiya007 Wed, 08/12/2020 - 05:59

Hi klmi,
 
It works perfectly now, thank you so much!