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!

       

      John

        • 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'+

          str(datepart('minute',[TIMESTAMP])))

           

          Jim

          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: