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:

blerinad9411 · 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"
  )
)

davidj137213 · Answer

Why don't you use it as a string instead of converting to date??

Sign up

Login with SSO

Login to the community

Login with SSO