TEXT
Updated: 30 April 2009
Use wct.TEXT to convert a numeric value to text and specify the display formatting by using special format strings.
Syntax
SELECT [wctString].[wct].[TEXT] (
<@Value, float,>
,<@Format, nvarchar(4000),>)
Arguments
@Value
is the value to be formatted. @Value is an expression of type float or of a type that can be implicitly converted to float.
@Format
A numeric format as a text string enclosed in quotation marks, for example 'm/d/yyyy' or '#,###.00'. The @Format argument can be of data types that are implicitly convertible to nvarchar or ntext.
Return Types
nvarchar(4000)
Remarks
· TEXT treats date formats by interpreting the number as the number of days since December 31, 1899, consistent with EXCEL. This is different than how SQL Server stores date in a datetime field.
Number formats
· Use zero (0) in @Format to display insignificant zeroes if @Value has fewer digits than there are in the format. For example, if you always wanted to display 5 decimal places to the right of the decimal, then @Format would end in '.00000'.
· Use the symbol (#) in @Format to suppress leading and trailing zeroes. If you wanted to display up to 5 decimal places, but no more, then @Format would end in '.#####'.
· Use the symbol (?) in @Format to display spaces in place of insignificant zeroes if @Value has fewer digits than there are in @Format. This could be used to make sure that the decimal places are lined up in the resultant table.
· Use the period (.) in @Format to display the decimal point in a number.
· Use the comma (,) in @Format as the thousands separator.
· A comma (,) at the end of @Format scales @Value by one thousand. You can use multiple commas; each one scaling by one thousand, so 2 commas would scale by one million, 3 commas by one billion, etc.
· Use the symbol (/) in @Format to identify that you want the result returned as a fraction. The (/) symbol can be used in conjunction with the other symbols. You cannot have more than 3 digits in the denominator. If you need fractions with more than 3 digits, use the DEC2FRAC function.
· Put the dollar grammalogue ($) at the beginning of @Format to display a currency value. You can specify other currencies by using the appropriate symbols: (¢), (£), (¥), and (€).
· Use the percent sign (%) in @Format to display @Value as a percent.
· Use the letter E (E, e, E+, E-, e+, e-) in @Format to display @Value in scientific notation. For example, if @Format = '0.00E+00' and @Value = 299792458, then the result would be 3.00E+08. If @Format = '0.###E+00', then the result would be 2.998E+08.
· Use the semi-colon (;) to identify different formats for positive and negative values or postive, negative, and zero values.
Date and time formats
· Use m to display the month as a number without a leading zero.
· Use mm to display the month as a number with a leading zero when appropriate.
· Use mmm to display the month as an abbreviation (Jan to Dec).
· Use mmmm to display the month as a full name (January to December).
· Use mmmmm to display the month as a single letter (J to D).
· Use d to displays the day as a number without a leading zero.
· Use dd to display the day as a number with a leading zero when appropriate.
· Use ddd to display the day as an abbreviation (Sun to Sat).
· Use dddd to display the day as a full name (Sunday to Saturday).
· Use yy to display the year as a two-digit number.
· Use yyyy to display the year as a four-digit number.
· Use h to display the hour as a number without a leading zero.
· Use [h] to display elapsed time in hours. If you are working with a formula that returns a time in which the number of hours exceeds 24, use a number format that resembles [h]:mm:ss.
· Use hh to display the hour as a number with a leading zero when appropriate. If the format contains AM or PM, the hour is shown based on the 12-hour clock. Otherwise, the hour is shown based on the 24-hour clock.
· Use m to Display the minute as a number without a leading zero. The m or the mm code must appear immediately after the h or hh code or immediately before the ss code; otherwise, the month is displayed instead of minutes.
· Use [m] to display elapsed time in minutes. If you are working with a formula that returns a time in which the number of minutes exceeds 60, use a number format that resembles [mm]:ss.
· Use mm to display the minute as a number with a leading zero when appropriate. The m or the mm code must appear immediately after the h or hh code or immediately before the ss code; otherwise, the month is displayed instead of minutes.
· Use s to display the second as a number without a leading zero.
· Use [s] to display elapsed time in seconds. If you are working with a formula that returns a time in which the number of seconds exceeds 60, use a number format that resembles [ss].
· Use ss to display the second as a number with a leading zero when appropriate. If you want to display fractions of a second, use a number format that resembles h:mm:ss.00.
· Use AM/PM, am/pm, A/P, or a/p to displays the hour based on a 12-hour clock.
Examples
These are some basic examples dealing with decimal places and significant digits. We have used a temporary table to simplify the examples.
CREATE TABLE #t (
[recno] [float] NOT NULL,
[num_val] [float] NOT NULL,
[num_format] [varchar](50) NOT NULL
)
INSERT INTO #t VALUES( 1, 1234567890, '#,#')
INSERT INTO #t VALUES( 2, 1234567890, '###')
INSERT INTO #t VALUES( 3, 1234567890, '###.##')
INSERT INTO #t VALUES( 4, 1234567890, '###.00')
INSERT INTO #t VALUES( 5, 22.98643, '###.000')
INSERT INTO #t VALUES( 6, 22.98643, '0.#')
INSERT INTO #t VALUES( 7, 22.98643, '#.0#')
INSERT INTO #t VALUES( 8, 44.398, '???.???')
INSERT INTO #t VALUES( 9, 102.65, '???.???')
INSERT INTO #t VALUES( 10, 2.8, '???.???')
INSERT INTO #t VALUES( 11, 5.25, '# ???/???')
INSERT INTO #t VALUES( 12, 5.3, '# ???/???')
select *, wct.TEXT(num_val, num_format)
from #t
This produces the following result
recno num_val num_format
----------- ---------------------- --------------- ----------------------------
1 1234567890 #,# 1,234,567,890
2 1234567890 ### 1234567890
3 1234567890 ###.## 1234567890.
4 1234567890 ###.00 1234567890.00
5 22.98643 ###.000 22.986
6 22.98643 0.# 23.
7 22.98643 #.0# 22.99
8 44.398 ???.??? 44.398
9 102.65 ???.??? 102.65
10 2.8 ???.??? 2.8
11 5.25 # ???/??? 5 1/4
12 5.3 # ???/??? 5 3/10
Scientific notation
CREATE TABLE #t (
[recno] [int] NOT NULL,
[num_val] [float] NOT NULL,
[num_format] [varchar](15) NOT NULL
INSERT INTO #t VALUES( 13, 1234567890, '0.00E00')
INSERT INTO #t VALUES( 14, 1234567890, '#.##E00')
INSERT INTO #t VALUES( 15, 1234567890, '0.00E+00')
INSERT INTO #t VALUES( 16, 1234567890, '#.##E+00')
INSERT INTO #t VALUES( 17, 1234567890, '0.00E-00')
INSERT INTO #t VALUES( 18, 1234567890, '#.##E-00')
INSERT INTO #t VALUES( 19, .00001234567890, '0.000E00')
INSERT INTO #t VALUES( 20, .00001234567890, '#.###E00')
INSERT INTO #t VALUES( 21, .00001234567890, '0.00E+00')
INSERT INTO #t VALUES( 22, .00001234567890, '#.##E+00')
INSERT INTO #t VALUES( 23, .00001234567890, '0.00E-0')
INSERT INTO #t VALUES( 24, .00001234567890, '#.##E-0')
select *, wct.TEXT(num_val, num_format)
from #t
This produces the following result
recno num_val num_format
----------- ---------------------- --------------- ---------------------------
13 1234567890 0.00E00 1.23E09
14 1234567890 #.##E00 1.23E09
15 1234567890 0.00E+00 1.23E+09
16 1234567890 #.##E+00 1.23E+09
17 1234567890 0.00E-00 1.23E09
18 1234567890 #.##E-00 1.23E09
19 1.23456789E-05 0.000E00 1.235E-05
20 1.23456789E-05 #.###E00 1.235E-05
21 1.23456789E-05 0.00E+00 1.23E-05
22 1.23456789E-05 #.##E+00 1.23E-05
23 1.23456789E-05 0.00E-0 1.23E-5
24 1.23456789E-05 #.##E-0 1.23E-5
(12 row(s) affected)
Thousands separator
CREATE TABLE #t (
[recno] [int] NOT NULL,
[num_val] [float] NOT NULL,
[num_format] [varchar](15) NOT NULL
)
INSERT INTO #t VALUES( 25, 1234567890, '#,###')
INSERT INTO #t VALUES( 26, 1234567890, '#,###,')
INSERT INTO #t VALUES( 27, 1234567890, '#,###,,')
select *, wct.TEXT(num_val, num_format)
from #t
This produces the following result
recno num_val num_format
----------- ---------------------- --------------- ----------------------------
25 1234567890 #,### 1,234,567,890
26 1234567890 #,###, 1,234,568
27 1234567890 #,###,, 1,235
(3 row(s) affected)
Dates
CREATE TABLE #t (
[recno] [int] NOT NULL,
[num_val] [float] NOT NULL,
[num_format] [varchar](20) NOT NULL
)
INSERT INTO #t VALUES( 28, 39933, 'mm/dd/yy')
INSERT INTO #t VALUES( 29, 39933, 'dd/mm/yy')
INSERT INTO #t VALUES( 30, 39933, 'd/m/yy')
INSERT INTO #t VALUES( 31, 39933, 'm/d/yy')
INSERT INTO #t VALUES( 32, 39933, 'mm/dd/yyyy')
INSERT INTO #t VALUES( 33, 39933, 'dd/mm/yyyy')
INSERT INTO #t VALUES( 34, 39933, 'd/m/yyyy')
INSERT INTO #t VALUES( 35, 39933, 'm/d/yyyy')
INSERT INTO #t VALUES( 36, 39933, 'yymmdd')
INSERT INTO #t VALUES( 37, 39933, 'dddd, dd mmmm yyyy')
INSERT INTO #t VALUES( 38, 39933, 'ddd, dd mmm yyyy')
INSERT INTO #t VALUES( 39, 39933, 'mmmmm-yyyy')
select *, wct.TEXT(num_val, num_format)
from #t
This produces the following result
recno num_val num_format
----------- ---------------------- -------------------- ----------------------
28 39933 mm/dd/yy 04/30/09
29 39933 dd/mm/yy 30/04/09
30 39933 d/m/yy 30/4/09
31 39933 m/d/yy 4/30/09
32 39933 mm/dd/yyyy 04/30/2009
33 39933 dd/mm/yyyy 30/04/2009
34 39933 d/m/yyyy 30/4/2009
35 39933 m/d/yyyy 4/30/2009
36 39933 yymmdd 090430
37 39933 dddd, dd mmmm yyyy Thursday, 30 April 2009
38 39933 ddd, dd mmm yyyy Thu, 30 Apr 2009
39 39933 mmmmm-yyyy A-2009
(12 row(s) affected)
Times
CREATE TABLE #t (
[recno] [int] NOT NULL,
[num_val] [float] NOT NULL,
[num_format] [varchar](20) NOT NULL
)
INSERT INTO #t VALUES( 40, 0.5892361, 'h:m:s')
INSERT INTO #t VALUES( 41, 0.5892361, 'hh:mm:ss')
INSERT INTO #t VALUES( 42, 0.5892361, 'h:mm:ss AM/PM')
INSERT INTO #t VALUES( 43, 0.5892361, 'h:mm AM/PM')
INSERT INTO #t VALUES( 44, 0.5892361, '[hh]:mm:ss')
INSERT INTO #t VALUES( 45, 0.5892361, '[mm]:ss')
INSERT INTO #t VALUES( 46, 0.5892361, '[ss]')
select *, wct.TEXT(num_val, num_format)
from #t
This produces the following result
recno num_val num_format
----------- ---------------------- -------------------- ----------------------
40 0.5892361 h:m:s 14:8:30
41 0.5892361 hh:mm:ss 14:08:30
42 0.5892361 h:mm:ss AM/PM 2:08:30 PM
43 0.5892361 h:mm AM/PM 2:08 PM
44 0.5892361 [hh]:mm:ss 14:08:30
45 0.5892361 [mm]:ss 848:30
46 0.5892361 [ss] 50910
(7 row(s) affected)
Date & Time
CREATE TABLE #t (
[recno] [int] NOT NULL,
[num_val] [float] NOT NULL,
[num_format] [varchar](25) NOT NULL
)
INSERT INTO #t VALUES( 47, 39933.5892361, 'm/d/yy h:m:s')
INSERT INTO #t VALUES( 48, 39933.5892361, 'mm/dd/yyyy hh:mm:ss')
INSERT INTO #t VALUES( 49, 39933.5892361, 'mm/dd/yyyy h:mm AM/PM')
INSERT INTO #t VALUES( 50, 39933.5892361, 'dddd, mm/dd/yy h:mm AM/PM')
select *, wct.TEXT(num_val, num_format)
from #t
This produces the following result
recno num_val num_format
----------- ---------------------- ------------------------- -------------------
47 39933.5892361 m/d/yy h:m:s 04/30/09 14:8:30
48 39933.5892361 mm/dd/yyyy hh:mm:ss 04/30/2009 14:08:30
49 39933.5892361 mm/dd/yyyy h:mm AM/PM 04/30/2009 2:08 PM
50 39933.5892361 dddd, mm/dd/yy h:mm AM/PM Thursday, 04/30/09 2:08 PM
(4 row(s) affected)
Percents, currencies, and semi-colons
CREATE TABLE #t (
[recno] [int] NOT NULL,
[num_val] [float] NOT NULL,
[num_format] [varchar](25) NOT NULL
)
INSERT INTO #t VALUES( 51, 1.5, '#.00%')
INSERT INTO #t VALUES( 52, -0.75, '#.00%')
INSERT INTO #t VALUES( 53, 1000000, '$#,#.00')
INSERT INTO #t VALUES( 54, -1000000, '$#,#.00')
INSERT INTO #t VALUES( 55, 1000000, '€#,#.00')
INSERT INTO #t VALUES( 56, -1000000, '€#,#.00;(€#,#.00)')
INSERT INTO #t VALUES( 57, 1000000, '¥#,#;(¥#,#)')
INSERT INTO #t VALUES( 58, -1000000, '¥#,#;(¥#,#)')
select *, wct.TEXT(num_val, num_format)
from #t
This produces the following result
recno num_val num_format
----------- ---------------------- ------------------------- -------------------
51 1.5 #.00% 150.00%
52 -0.75 #.00% -75.00%
53 1000000 $#,#.00 $1,000,000.00
54 -1000000 $#,#.00 -$1,000,000.00
55 1000000 €#,#.00 €1,000,000.00
56 -1000000 €#,#.00;(€#,#.00) (€1,000,000.00)
57 1000000 ¥#,#;(¥#,#) ¥1,000,000
58 -1000000 ¥#,#;(¥#,#) (¥1,000,000)