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:
Discussion posts and replies are publicly visible
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" ) )
Are you aware that the tostring() function does not provide any means of formatting and no second parameter? You should try the text() function instead.
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" ) )
Keep in mind, that this method might or might not display the date value in the wrong timezone. The reason is, that the string in the DB probably is stored in UTC. Then you would want, or maybe not, convert the value into the users timezone.
The datetime() function does not perform this conversion. If you need that, use userdatetime().
And for more background on this, read my blog post: https://appian.rocks/2023/02/13/working-with-time-in-appian/