leading zeros in date field
Altair EmployeeAnswers
-
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.
0 -
Altair Forum User said:
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.
Perfect!!!
Thanks for your help.
Dan
0