Issue with importing date field with time value from text file

3 posts / 0 new
Last post
ijaiya007
Offline
Joined: 10/28/2019 - 11:42
Issue with importing date field with time value from text file

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
Offline
Joined: 02/13/2019 - 08:41

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
Offline
Joined: 10/28/2019 - 11:42

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