Problem:
I have imported the following data into Spotfire as String column. If I then change the data type from String to Datetime, all of the value is turned into NULL.
|
myDate |
|
10NOV2022:04:52:09.200 |
|
02SEP1999:02:50:31.613 |
| 20JAN2020:05:03:37.833 |
Resolution:
Spotfire's Datetime data type does not support milliseonds, so we must remove this portion of the string before parsing it to Datetime.
The following expression will remove the milliseconds part and then parse the remaining portion using custom informat "ddMMMyyy" and finally return Datetime value.
ParseDateTime(Split([myDate], ".", 1),"ddMMMyyyy:HH:mm:ss","en-US")
You can add a new calculated column OR you can replace the existing column by [Calculate and replace column] transformation.