Expression/calculation function in Spotfire to convert a Date to DateTime

[Iredia Osadolor]

Is there an Expression/calculation function in Spotfire to convert a Date, data type String value eg. 20200218 to DateTime such as 2/18/2020 11:35:34 or 2-18-2020 11:35:34. I have tried using transform data->data table->change data types->add-new data types->DateTime->formatting to 2/18/2020 11:35:34 but the output came back empty.

[Tyger Guzman 2]

Use the DateTime() Function:

and substring :

 

Keep in mind that DateTime function only accepts Integers.

Returns the substring of Arg1 starting at index Arg2 with a length of Arg3 characters.

 

Example:

Substring("Hello", 2, 3)Creates a DateTime from a column or values. If a single argument is given, the input column can be of type String or Date. If a String is specified, the date must be written on a form that Spotfire can recognize and all parts of the date (year, month and day) must be present. If a Date is specified, the time part is set to 00:00:00.

If seven integer arguments are given, then the first argument is the year, the second is the month, the third is the day of the month, the fourth is the hour, the fifth is the minute, the sixth is the second and the seventh is the millisecond.

 

Example:

DateTime([Column])

DateTime(2009, 05, 17, 11, 30, 20, 4)DateTime(

Integer(Substring([WO Created Date],1,4)),

Integer(Substring([WO Created Date],5,2)),

Integer(Substring([WO Created Date],7,2)),

11,

35,

34,

0)

[Iredia Osadolor]

Thanks, tuger0951. however, Spotfire didn't like it. I have attached it to the message.

[Tyger Guzman 2]

I've udpated the answer to reflect the function having Integer() around each substring : 

 

 

 

DateTime(
Integer(Substring([WO Created Date],1,4)),
Integer(Substring([WO Created Date],5,2)),
Integer(Substring([WO Created Date],7,2)),
11,
35,
34,
0)

 

 

[Jagrata Minardi]

You can use the ParseDateTime() expression function to create a DateTime Column fromthe String Column, and then use the DateAdd() expression function successively to add the "time" part 11:35:34.

DateAdd("ss", 34, DateAdd("mi", 35, DateAdd("hh", 11, ParseDateTime([WO Create Date], "yyyyMMdd"))))