CONVERT() function in SQL
Definition and Usage
The CONVERT() function is a general function that converts an expression of one data type to another.
The CONVERT() function can be used to display date/time data in different formats.
Syntax
Value | Description |
---|---|
data_type(length) | Specifies the target data type (with an optional length) |
expression | Specifies the value to be converted |
style | Specifies the output format for the date/time |
The table below represent the style values for datetime or smalldatetime conversion to character data:
Value (century yy) |
Value (century yyyy) |
Input/Output | Standard |
---|---|---|---|
- | 0 or 100 | mon dd yyyy hh:miAM (or PM) | Default |
1 | 101 | mm/dd/yy | USA |
2 | 102 | yy.mm.dd | ANSI |
3 | 103 | dd/mm/yy | British/French |
4 | 104 | dd.mm.yy | German |
5 | 105 | dd-mm-yy | Italian |
6 | 106 | dd mon yy | |
7 | 107 | Mon dd, yy | |
8 | 108 | hh:mm:ss | |
- | 9 or 109 | mon dd yyyy hh:mi:ss:mmmAM (or PM) | Default+millisec |
10 | 110 | mm-dd-yy | USA |
11 | 111 | yy/mm/dd | Japan |
12 | 112 | yymmdd | ISO |
- | 13 or 113 | dd mon yyyy hh:mi:ss:mmm (24h) | |
14 | 114 | hh:mi:ss:mmm (24h) | |
- | 20 or 120 | yyyy-mm-dd hh:mi:ss (24h) | |
- | 21 or 121 | yyyy-mm-dd hh:mi:ss.mmm (24h) | |
- | 126 | yyyy-mm-ddThh:mi:ss.mmm (no spaces) | ISO8601 |
- | 130 | dd mon yyyy hh:mi:ss:mmmAM | Hijiri |
- | 131 | dd/mm/yy hh:mi:ss:mmmAM | Hijiri |
Example
The following script uses the CONVERT() function to display different formats. We will use the GETDATE() function to get the current date/time:
CONVERT(VARCHAR(10),GETDATE(),10)
CONVERT(VARCHAR(10),GETDATE(),110)
CONVERT(VARCHAR(11),GETDATE(),6)
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)
The result would look something like this:
11-04-11
11-04-2011
04 Nov 11
04 Nov 2011
04 Nov 2011 11:45:34:243
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。