Formatting Numeric and DateTime Values

Formatting Numeric and Date/Time Values

Both numeric and date/time values allow for standard and custom formatting strings. A standard formatting string refers to a single character that specifies the desired output format for a value.

Any numeric format string that does not fit the definition of a standard format string (either numeric or date/time) is interpreted as a custom format string. For example, format string " d!" is interpreted as a custom format string because it contains two alphabetic characters , even though the character "d" is a standard date/time format specifier . This is true even if the extra characters are white spaces. Similarly, character "h" is interpreted as a custom format because it does not match any standard format specifiers.

In addition, developers can exercise greater control over how values are formatted by using custom format specifiers.

Resulting output strings are influenced by the settings in the Regional Options control panel and locale control properties: >Language , NumeralLanguage , and NumeralVariant . Computers with different locale-specific settings generate different formatting for numbers and dates.

For example, February 4, 2006 returns 2/4/2006 when run with the United States English (en-US) locale, but it returns 04.02.2006 when run with the German (de-DE) locale.

You can learn more about globalization and locale options at http://www.microsoft.com/globaldev/getWR/steps/WRG_lclmdl.mspx.

Standard Numeric Format Strings

Numeric format strings are used to format common numeric types. A standard format string takes the form Axx , where A is a single alphabetic character called the format specifier, and xx is an optional integer called the precision specifier. The format specifier must be one of the built-in format characters (see Table 13.3). The precision specifier ranges from 0 to 99 and controls the number of significant digits or zeros to the right of a decimal. The format string cannot contain white spaces.

If the format string does not contain one of the standard format specifiers, the format string is ignored.

The following abbreviations are used: letter 'd' indicates a digit (0-9), letter 'E' or 'e' denotes an exponent, ' ±' indicates that you can use either the plus or minus sign in the expression. The exponent always consists of a plus or minus sign and a minimum of three digits. By default, SSRS prefixes negative numbers with a minus sign.

The precision specifier indicates the minimum number of digits desired. If required, the number is padded with zeros to produce the number of digits given by the precision specifier. The number is padded to its left for integer types formatted with Decimal or Hexadecimal specifiers and in digits after the decimal point for other specifiers. Padding is ignored for the 'R' format specifier.

Table 13.3. Standard Numeric Format Strings

Format Specifier	Name	Description	Example
C or c	Currency	The number is converted to a string that represents a currency amount. The conversion is controlled by the Language property. The precision specifier indicates the desired number of decimal places; if omitted, the default currency precision is controlled by the Language property.	Value=1234.567 Language =default Output=$1,234.57 Language ="English (United Kingdom)"Output= & pound ;1,234.57
D or d	Decimal	This format is supported for integer types only. The number is converted to a string of decimal digits (0-9).	Value=1234 Output=1234
E or e	Scientific (exponential)	The number is converted to a string of the form d.ddd...E ±ddd or d.ddd...e ±ddd . One digit always precedes the decimal point, a minimum of three digits follow the ± sign , and the case determines the prefix of the exponent (' E ' or an ' e '). If the precision specifier is omitted, a default of ' E6 ' is used.	Value=1234.567 Output= 1.234567+E003
F or f	Fixedpoint	The number is converted to a string of the form ddd.ddd... . If the precision specifier is omitted, the default numeric precision given by the Language property is used.	Value=1234.567 Output=1234.57
G or g	General	The number is converted to the most compact of either fixed-point or scientific notation, depending on the type of number and whether a precision specifier is present. If the precision specifier is omitted or zero, the type of the number determines the default precision.	Value=1234.567 Output=1234.567
N or n	Number	The number is converted to a string of the form d, ddd, ddd.ddd... . Thousand separators are inserted between each group of three digits to the left of the decimal point. If the precision specifier is omitted, the precision is guided by the Language property.	Value=1234.567 Output=1,234.567
P or p	Percent	The number is converted to a string where the value is multiplied by 100% and presented with a percentage sign.If the precision specifier is omitted, the precision is guided by the Language property.	Value=123.4567 Output=12,345.67
R or r	Round-trip	The round-trip specifier guarantees that a numeric value converted to a string will be parsed back into the same numeric value. SSRS examines data for the best output to accomplish this.	Value=1234.567 Output=1234.567
X or x	Hexadecimal	The number is converted to a string of hexadecimal digits. 'X' produces uppercase ("ABCDEF") for digits greater than 9, 'x' produces "abcdef".Decimal number '123' is correspondingly converted to hexadecimal '7b'. This format is supported for integer types only.	Value=1234 Output=4D2

Additional information about standard numeric format strings can be found in the .NET Framework Developer's Guide at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp.

Custom Numeric Format Strings

Table 13.4 shows custom numeric format strings, descriptions, and output examples. You can exercise greater control over how values are formatted by using custom format specifiers.

Table 13.4. Custom Numeric Format Strings

Format Specifier	Name	Description	Example
	Zero placeholder	In the position where the '0' appears in the format string, copy the digit of the value to the result string. Number of 0s before and after the decimal point determines exact number of digits. The number is rounded to the nearest decimal position.	Value=012.3 Format=000.00 Output=012.30 Format=000. Output=012
#	Digit placeholder	In the position where the '#' appears in the format string, copy the digit of the value to the result string. This specifier never displays '0' character if it is not a significant digit, even if '0' is the only digit in the string.	Value=012.30 Format=###.## Output=12.3 Format phone number Value=1234567890 Format(###) ###-#### Output=[phone]
.	Decimal point	The first '.' character in the format string determines the location of the decimal separator in the formatted value; any additional '.' characters are ignored. The actual character used as the decimal separator is determined by the Language property. For example, the French decimal point is actually a comma ','.	Value=12.34 Format=###.## Language= " French " Output=12,3
,	Thousand separator and number scaling	First occurrence of ',' when used in conjunction with '0' and '#' placeholders inserts thousand separators between each group of three digits to the left of the decimal separator. The actual character used as the decimal separator is determined by the Language property.	Value=10000000.5 Format=#,###,. Output=10,000
%	Percentage placeholder	Multiply the value by 100 and insert the % sign. The percent character used is dependent on the current Language .	Value=10.5 Format=###% Output=1050
E0 E ±0 e0 e ±0	Scientific notation	Format using scientific notation. The number of '0's determines the minimum number of digits to output for the exponent. The "E+" and "e+" formats indicate that a sign character (plus or minus) should always precede the exponent. The "E", "E-", "e", or "e-" formats indicate that a sign character should only precede negative exponents.	Value=12000 Format=0.###E+000 Output=12E+004
'ABC' " ABC "	Literal string	Characters enclosed in single or double quotes are copied to the result string literally, and do not affect formatting.	Value=12 Format=F "AB"00.00 Output=AB12.00
;	Section separator	The ';' character is used to apply separate formatting for positive, negative, and zero numbers in the format string. If there are two sections and the result is zero, it is formatted per the first section. If the second section is skipped , such as ##;;0, then 0 is formatted according to the third section.	Value=-123 Format=##;(##) Output=(123)
\c	Escape character	Where c is any character, the escape character displays the next character as a literal. In this context, the escape character cannot be used to create an escape sequence (such as "\n" for newline).	Value=-123 Format= \z0000 Output=-z0123
Other	All other characters	All other characters are copied to the result string as literals in the position they appear.

Numbers are rounded to as many decimal places as there are digit placeholders to the right of the decimal point. If the format string does not contain a decimal point, the number is rounded to the nearest integer. If the number has more digits than there are digit placeholders to the left of the decimal point, the extra digits are copied to the result string immediately before the first digit placeholder.

Additional information about custom numeric format strings can be found in the .NET Framework Developer's Guide at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp.