Definition and Usage
The CONVERT() function converts a value (of any type) into a specified datatype.
Tip:
Also look at the
CAST()
function.
Syntax
CONVERT(
data_type(length)
,
expression
,
style
)
Parameter Values
Value
Description
data_type
Required. The datatype to convert
expression
to. Can be one of
the following: bigint, int, smallint, tinyint, bit, decimal, numeric, money,
smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar,
nvarchar, ntext, binary, varbinary, or image
(length)
Optional. The length of the resulting data type (for char, varchar,
nchar, nvarchar, binary and varbinary)
expression
Required. The value to convert to another data type
style
Optional. The format used to convert between data types, such as a date
or string format. Can be one of the following values:
Converting datetime to character:
Without century
With century
Input/Output
Standard
0100mon dd yyyy hh:miAM/PMDefault
1101mm/dd/yyyy US
2102 yyyy.mm.ddANSI
3103 dd/mm/yyyyBritish/French
4104dd.mm.yyyyGerman
5105 dd-mm-yyyyItalian
6106dd mon yyyy-
7107Mon dd, yyyy-
8108hh:mm:ss-
9109mon dd yyyy hh:mi:ss:mmmAM (or PM)Default + millisec
10110mm-dd-yyyyUSA
11111 yyyy/mm/ddJapan
12112 yyyymmddISO
13113dd mon yyyy hh:mi:ss:mmm
Europe (24 hour clock)>
14114hh:mi:ss:mmm24 hour clock
20120yyyy-mm-dd hh:mi:ssODBC canonical (24
hour clock)
21121yyyy-mm-dd hh:mi:ss.mmmODBC canonical
(24 hour clock)
126yyyy-mm-ddThh:mi:ss.mmmISO8601
127yyyy-mm-ddThh:mi:ss.mmmZISO8601 (with time zone Z)
130dd mon yyyy hh:mi:ss:mmmAMHijiri
131dd/mm/yy hh:mi:ss:mmmAMHijiri
Converting float to real:
Value
Explanation
0Maximum 6 digits (default)
18 digits
216 digits
Converting money to character:
Value
Explanation
0No comma delimiters, 2 digits to the right of decimal
1Comma delimiters, 2 digits to the right of decimal
2 No comma delimiters, 4 digits to the right of decimal
Convert an expression from one data type to another (varchar):
SELECT CONVERT(varchar, 25.65);
Try it Yourself »
Example
Convert an expression from one data type to another (datetime):
SELECT CONVERT(datetime, '2017-08-25');
Try it Yourself »
Example
Convert an expression from one data type to another (varchar):
SELECT CONVERT(varchar,
'2017-08-25', 101);
Try it Yourself »
❮
Previous
❮ SQL Server Functions
Next
❯
Contact Sales
If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
[email protected]
Report Error
If you want to report an error, or if you want to make a suggestion, send us an e-mail:
[email protected]
W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning.
Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness
of all content. While using W3Schools, you agree to have read and accepted our
terms of use
,
cookie and privacy policy
.
W3Schools is Powered by W3.CSS
.
