Trackback URL : http://zxcasd.pe.kr/tt/trackback/186
아름다운 기사
Trackback URL : 이 글에는 트랙백을 보낼 수 없습니다
출처 : http://msdn.microsoft.com/en-us/library/ms187928.aspx
Converts an expression of one data type to another.
.gif)
Transact-SQL Syntax Conventions
Arguments Is any valid expression.
Is the target data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used. For more information about available data types, see Data Types (Transact-SQL).
Is an optional integer that specifies the length of the target data type. The default value is 30.
Is an integer expression that specifies how the CONVERT function is to translate expression. If style is NULL, NULL is returned. The range is determined by data_type. For more information, see the Remarks section.
Remarks When expression is a date or time data type, style can be one of the values shown in the following table. Other values are processed as 0. SQL Server supports the date format in Arabic style by using the Kuwaiti algorithm.
| Without century (yy) (1) | With century (yyyy) | Standard | Input/Output (3) |
|---|---|---|---|
|
- |
0 or 100 (1, 2) |
Default |
mon dd yyyy hh:miAM (or PM) |
|
1 |
101 |
U.S. |
mm/dd/yyyy |
|
2 |
102 |
ANSI |
yy.mm.dd |
|
3 |
103 |
British/French |
dd/mm/yyyy |
|
4 |
104 |
German |
dd.mm.yy |
|
5 |
105 |
Italian |
dd-mm-yy |
|
6 |
106 (1) |
- |
dd mon yy |
|
7 |
107 (1) |
- |
Mon dd, yy |
|
8 |
108 |
- |
hh:mi:ss |
|
- |
9 or 109 (1, 2) |
Default + milliseconds |
mon dd yyyy hh:mi:ss:mmmAM (or PM) |
|
10 |
110 |
USA |
mm-dd-yy |
|
11 |
111 |
JAPAN |
yy/mm/dd |
|
12 |
112 |
ISO |
yymmdd yyyymmdd |
|
- |
13 or 113 (1, 2) |
Europe default + milliseconds |
dd mon yyyy hh:mi:ss:mmm(24h) |
|
14 |
114 |
- |
hh:mi:ss:mmm(24h) |
|
- |
20 or 120 (2) |
ODBC canonical |
yyyy-mm-dd hh:mi:ss(24h) |
|
- |
21 or 121 (2) |
ODBC canonical (with milliseconds) |
yyyy-mm-dd hh:mi:ss.mmm(24h) |
|
- |
126 (4) |
ISO8601 |
yyyy-mm-ddThh:mi:ss.mmm (no spaces) |
|
- |
127(6, 7) |
ISO8601 with time zone Z. |
yyyy-mm-ddThh:mi:ss.mmmZ (no spaces) |
|
- |
130 (1, 2) |
Hijri (5) |
dd mon yyyy hh:mi:ss:mmmAM |
|
- |
131 (2) |
Hijri (5) |
dd/mm/yy hh:mi:ss:mmmAM |
1 These style values return nondeterministic results. Includes all (yy) (without century) styles and a subset of (yyyy) (with century) styles.
2 The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).
3 Input when you convert to datetime; output when you convert to character data.
4 Designed for XML use. For conversion from datetime or smalldatetime to character data, the output format is as described in the previous table.
5 Hijri is a calendar system with several variations. SQL Server uses the Kuwaiti algorithm.
.gif) Important: |
|---|
| By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on Automation objects, use a cutoff year of 2030. SQL Server provides the two digit year cutoff configuration option that changes the cutoff year used by SQL Server and allows for the consistent treatment of dates. We recommend specifying four-digit years. |
6 Only supported when casting from character data to datetime or smalldatetime. When character data that represents only date or only time components is cast to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.
7The optional time zone indicator, Z, is used to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Z is the indicator for time zone UTC-0. Other time zones are indicated with HH:MM offset in the + or - direction. For example: 2006-12-12T23:45:12-08:00.
When you convert to character data from smalldatetime, the styles that include seconds or milliseconds show zeros in these positions. You can truncate unwanted date parts when you convert from datetime or smalldatetime values by using an appropriate char or varchar data type length.
When you convert to datetimeoffset from character data with a style that includes a time, a time zone offset is appended to the result.
-----------------------------------------------------------------------------
select convert(varchar, getdate(),0)
select convert(varchar, getdate(),1)
select convert(varchar, getdate(),2)
select convert(varchar, getdate(),3)
select convert(varchar, getdate(),4)
select convert(varchar, getdate(),5)
select convert(varchar, getdate(),6)
select convert(varchar, getdate(),7)
select convert(varchar, getdate(),8)
select convert(varchar, getdate(),9)
select convert(varchar, getdate(),10)
select convert(varchar, getdate(),11)
select convert(varchar, getdate(),12)
select convert(varchar, getdate(),13)
select convert(varchar, getdate(),14)
select convert(varchar, getdate(),20)
select convert(varchar, getdate(),21)
select convert(varchar, getdate(),22)
select convert(varchar, getdate(),23)
select convert(varchar, getdate(),24)
select convert(varchar, getdate(),25)
select convert(varchar, getdate(),100)
select convert(varchar, getdate(),101)
select convert(varchar, getdate(),102)
select convert(varchar, getdate(),103)
select convert(varchar, getdate(),104)
select convert(varchar, getdate(),105)
select convert(varchar, getdate(),106)
select convert(varchar, getdate(),107)
select convert(varchar, getdate(),108)
select convert(varchar, getdate(),109)
select convert(varchar, getdate(),110)
select convert(varchar, getdate(),111)
select convert(varchar, getdate(),112)
select convert(varchar, getdate(),113)
select convert(varchar, getdate(),114)
select convert(varchar, getdate(),120)
select convert(varchar, getdate(),121)
select convert(varchar, getdate(),130)
select convert(varchar, getdate(),131)
Trackback URL : 이 글에는 트랙백을 보낼 수 없습니다
