ToText() function throws in an extra space.

Thursday 26 February 2015 @ 5:48 am

You can use the ToText () function to turn dates into strings, which is handy in cases when you need to combine a date with other characters. For instance I use this pattern in IF-THEN-ELSE formulas, when I want to give the user a parameter to choose sorting on a date value vs a string value:

else ToText ({Tale.Date} , 'yyyy-MM-dd')

This turns the date into an 8 character string that can still be used as a sort field and will still sort the records in chronological order. The [yyyy] returns a 4-digit year, the [MM] in the middle returns a 2-digit (0 padded) month and the [dd] on the end returns a 2-digit (0 padded) day. The dashes can be any character that you want to use as a divider. So using the formula above the date value 2/4/2015 ends up looking like this:


For purposes other than sorting you might want a 2-digit year or a month/day value that is not zero padded. In that case this formula:

ToText ({Tale.Date} , 'yy-M-d')

creates a date that looks like this:

15-2- 4

The digits are correct, but if you look closely you will see an extra space in front of the days value. For some reason a single-digit month value does not have a space but a single-digit day value does have a space. This must be a bug since I can’t think of any reason for the difference. And it is a long standing bug since I just tested this in v10 (2004) and v8.5 (2001) and both have the exact same behavior.
If you ever encounter this and want to remove the space from in front of a single digit day, you have to use a replace function like this:

Replace ( ToText ({Tale.Date} , 'yy-M-d') , " " , "" )

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro