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

CONVERT(data_type(length),expression,style)

 

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(19),GETDATE())
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:

Nov 04 2011 11:45 PM
11-04-11
11-04-2011
04 Nov 11
04 Nov 2011
04 Nov 2011 11:45:34:243

CONVERT() function in SQL,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。