Microsoft® SQL Server® 2012 Bible

208

Part II: Building Databases and Working with Data

SQL Server also provides numeric formatting styles, but numeric formatting is typically the

task of the user interface, not the database.

TABLE 8-7 Convert Function Date Styles

Style Description Format

0/100* Default mon dd yyyy hh:miAM (or PM)

1/101 USA mm/dd/y y

2/102 ANSI yy.mm.dd

3/103 British/French dd/mm/yy

4/104 German dd.mm.yy

5/105 Italian dd-mm-yy

6/106 — dd mon yy

7/107 — mon dd, yy

8/108 — hh:mm:ss

9 or 109* Default+milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)

10 or 110 USA mm-dd-yy

11 o r 111 Ja p a n y y/m m /d d

12 or 112 ISO y ymmdd

13 or 113* Europe default+milliseconds dd mon yyyy hh:mm:ss:mmm (24h)

14 or 114 — hh:mi:ss:mmm (24h)

20 or 120* ODBC canonical yyyy-mm-dd hh:mi:ss (24h)

21 or 121* ODBC canonical + milliseconds yyyy-mm-dd hh:mi:ss.mmm (24h)

126 ISO8601 for XML use yyyy-mm-dd Thh:mm:ss:mmm (no spaces)

127 ISO8601 with time zone Z yyyy-mm-ddThh:mi:ss.mmmZ

130 Kuwaiti dd mon yyyy hh:mi:ss:mmmAM (or PM)

131 Kuwaiti dd/mm/yy hh:mi:ss:mmmAM (or PM)

* Both styles return dates with centuries.

Best Practice

In a clean client/server design, the server provides the data in an internal format, and the client applica-

tion formats the data as required by the user. Unformatted data is more independent than formatted

data and can be used by more applications.

c08.indd 208c08.indd 208 7/30/2012 4:21:17 PM7/30/2012 4:21:17 PM

http://www.it-ebooks.info