2 Replies Latest reply on May 17, 2018 2:44 PM by Bryce Larsen

    Convert Date/Time to a string field

    John Murphy

      Hello all,


      I have a data source that has the following type of date/time -  11/5/2016 5:04:25 PM


      Want to turn it into a string like the following - 17:04


      I'm trying the following calculated field - str(datepart('hour', [TIMESTAMP]))+':'+str(datepart('minute',[TIMESTAMP])) which gets me 17:4 instead


      How do I adjust this calculation to get the zero in there where it should be?


      Thanks in advance!



        • 1. Re: Convert Date/Time to a string field
          Jim Dehner

          H John

          Try the change below - I was not able to check it out so you may need to play with it a little - the idea is to pack the minute filed to at least 3 string characters and then take the 2 characters on the right





          str(datepart('hour', [TIMESTAMP]))+':'+right('00'+




          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Convert Date/Time to a string field
            Bryce Larsen

            Just adding to Jim's (correct) comment - need to add ,2 at the end of the RIGHT() statement so you take only the right two characters of the # minutes. You could do the same thing for the hours if you'd always like there to be two characters. (eg. 05:00 for 5am).


            Another approach using the modulo operator: