2 Replies Latest reply: Mar 16, 2017 10:33 AM by a2z143 _ RSS

    leading zeros in date field

    a2z143 _

      need help in changing dates from 7222017 to 07222017

        • Re: leading zeros in date field
          Steve Caiels

          Hi,

           

          If it's a date field, you can select Long Date, Short Date etc from the properties of the field, but leading zeros and/or separators are controlled by the Windows regional settings of your PC.

           

          M/d/Y will result in a date displaying as 1/1/17  and MM/DD/YYYY will result in 01/01/2017 for example.

           

          If it's a character field, then it' depends on whether you can tell the month and day apart.  Would 1112017 be 11th Jan or 1st November - or could it be either?   If it's only ever the ultimate leading zero that is missing and therefore you would 11012017, then the following calculation will fix it.

          Right("0" + [YourOriginalFieldName],8)

           

          0" + [YourOriginalFieldName] will add the leading zero, whether you need it or not;

          "12122017" goes to 012122017 for 12th December

          "1012017" goes to 01012017 for 1st January

           

          Then take the right hand 8 characters with Right("0" + [YourOriginalFieldName],8);

          "012122017" trims back to 12122017 for 12th December

          "01012017" stays at 01012017 for 1st January

           

          Finally, if it's a numeric field, you can convert it to character and add the leading zero using the expression

          Str([YourOriginalFieldName],8,0,"0")

           

          Regards,

          Steve.