Convert string to timestamp(6)

Question

Hello everyone, 
 I have a field for created date of type varchar(255) and it has the value in string, for example: "20220505101000345600", what I did before was converting it to date before saving it to database with this expression: 
 concat( mid( index( ri!pulsarMessageValue, "CREATION_TIMESTAMP", null ), 5, 2 ), "/", mid( index( ri!pulsarMessageValue, "CREATION_TIMESTAMP", null ), 7, 2 ), "/", left( index( ri!pulsarMessageValue, "CREATION_TIMESTAMP", null ), 4 ) ), 
 but now I am required to do store it as timestamp so then I can show it in the interface. My question is if is there any way to store it as datetime and then in the interface part I can use the formatted column or I need to convert it in the interface like this:

blerinadurguti · Accepted Answer

Yes, that's what I did as well but instead of tostring I used the datetext function. Thanks 
 a!localVariables(
 local!pulsarMessageValue: "20220505101000345600",
 local!dateTime: datetime(
 left(local!pulsarMessageValue, 4),
 mid(local!pulsarMessageValue, 5, 2),
 mid(local!pulsarMessageValue, 7, 2),
 mid(local!pulsarMessageValue, 9, 2),
 mid(local!pulsarMessageValue, 11, 2),
 left(mid(local!pulsarMessageValue, 13, 8), 2)
 ),
 datetext(
 local!dateTime,
 "HH:mm:ss, dd.MM.yyyy"
 )
)

Shubham Aware · Answer

Hello blerinadurguti , Based on your input i tried converting text to dateTime verify and make changes according to your need. a!localVariables(
 local!pulsarMessageValue: "20220505101000345600",
 local!dateTime: datetime(
 int(left(local!pulsarMessageValue, 4)),
 int(mid(local!pulsarMessageValue, 5, 2)),
 int(mid(local!pulsarMessageValue, 7, 2)),
 int(mid(local!pulsarMessageValue, 9, 2)),
 int(mid(local!pulsarMessageValue, 11, 2)),
 int(
 left(mid(local!pulsarMessageValue, 13, 8), 2)
 )
 ),
 text(
 local!dateTime,
 "yyyy-MM-dd HH:mm:ss"
 )
) Let me know if that works for you.

Convert string to timestamp(6)

Top Replies