SQL CONVERT Function

The CONVERT function converts values from one data type to another.

The format of the converted data type may also be specified.

If the conversion fails, an error is returned.

Example

#

This example converts values to other data types.

SELECT CONVERT(INT, 28117.2632) AS Int, 
       CONVERT(CHAR, GETDATE(), 108) AS Char,
       CONVERT(NVARCHAR(12), GETDATE(), 107) AS NVarchar 

Note: Formatting is specified with an optional style parameter (the third parameter).

Result:  1 record
Int Char NVarchar
28117 12:34:48 Oct 14, 2021

Syntax

Syntax of the CONVERT function.

CONVERT (data_type(length), expression, style)  

data_type -- data type the expression must be converted to.

length -- optional, the length of the result data type.

expression -- the value to be converted.

style -- optional, a format to be used to the result.

These are the Date and Time styles for CONVERT.

Style Standard Result
100 Datetime and smalldatetime mon dd yyyy hh:miAM (or PM)
101 US mm/dd/yyyy
102 ANSI yyy.mm.dd
103 British/French dd/mm/yyyy
104 German dd.mm.yyyy
105 Italian dd-mm-yyyy
106 -- dd mon yyyy
107 -- Mon dd, yyyy
108 -- hh:mi:ss
109 Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
110 USA mm-dd-yyyy
111 Japan yyyy/mm/dd
112 ISO yyyymmdd
113 Europe Default + milliseconds dd mon yyyy hh:mi:ss:mmm (24h)
114 -- hh:mi:ss:mmm (24h)
120 ODBC canonical yyyy-mm-dd hh:mi:ss (24h)
121 ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset yyyy-mm-dd hh:mi:ss.mmm (24h)
126 ISO8601 yyyy-mm-ddThh:mi:ss.mmm (no spaces)
127 ISO8601 with time zone Z yyyy-MM-ddThh:mm:ss.fffZ (no spaces)

Next are the float and real styles for CONVERT.

Style Result
0 6 digits max. Use scientific notation, when appropriate.
1 8 digits. Always use scientific notation.
2 16 digits. Always use scientific notation.

Finally, are the money and smallmoney styles for CONVERT.

Style Result
0 No commas and 2 decimals. Example: 28117.26.
1 Commas and 2 decimals. Example: 28,117.26.
2 No commas and 4 decimals. Example: 28117.2693.
126 Equivalent to 2, when converting to char(n) or varchar(n).

More Examples

CONVERT. DATETIME to STRING

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List orders with the order date formatted as mon dd yyyy hh:miAM/PM.
SELECT FirstName, LastName, OrderNumber,
       CONVERT(NVARCHAR, OrderDate, 100) AS OrderDate 
  FROM [Order] O
  JOIN Customer C ON C.Id = O.CustomerId
Result:  830 records
FirstName LastName OrderNumber OrderDate
Paul Henriot 542378 Jul 4 2012 12:00AM
Karin Josephs 542379 Jul 5 2012 12:00AM
Mario Pontes 542380 Jul 8 2012 12:00AM
Mary Saveley 542381 Jul 8 2012 12:00AM
Pascale Cartrain 542382 Jul 9 2012 12:00AM
Note: If no time is specified, it will be set to 0.

CONVERT. To STRING.

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List orders in a human-readable format.
SELECT 'On ' + CONVERT(NVARCHAR, OrderDate, 107) + 
       ' ' +  FirstName + ' ' + LastName + 
       ' placed an order for $' +  
       CONVERT(NVARCHAR, TotalAmount) AS Text
  FROM [Order] O
  JOIN Customer C ON C.Id = O.CustomerId
Result:  830 records
Text
On Jul 04, 2012 Paul Henriot placed an order for $440.00
On Jul 05, 2012 Karin Josephs placed an order for $1863.40
On Jul 08, 2012 Mario Pontes placed an order for $1813.00
On Jul 08, 2012 Mary Saveley placed an order for $670.80
On Jul 09, 2012 Pascale Cartrain placed an order for $3730.00
On Jul 10, 2012 Mario Pontes placed an order for $1444.80

CONVERT versus CAST

#

Both functions are commonly used in data conversions, but there are differences.

CAST is an ANSI Standard and is supported on SQL Server, MySQL, ORACLE, and others.

CONVERT is Microsoft specific and works only on SQL Server

CAST limits formatting to enable data conversions.

CONVERT on the other hand, can be used for formatting date/time, and money data types.

CAST has a simple syntax.

CONVERT offers tranlation and formatting flexibility with the style parameter.


You may also like



Guides


vsn 3.1