Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » System FunctionsRSS Feeds

CONVERT Function

Posted By: Sarita Patel     Category: SQL Server     Views: 7420

This article explains about CONVERT function in sql server with examples.

CONVERT function is used to explicitly convert an expression of one data type to another. 


Syntax of CONVERT Function :

CONVERT ( data_type [ ( length ) ] ,expression [ ,style ] )

data_type is sql server data type to convert expression into that data type.

length is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types.

expression is any valid sql server expression.

style is a style of date format used to convert datetime or smalldatetime data to character data or the string format when converting float, real, money, or smallmoney data to character data.

Below is the table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).

Year(yy)

Year(yyyy)

Standard

Input or Output

-

0 or 100 (*)

Default

mon dd yyyy hh:miAM (or PM)

1

101

USA

mm/dd/yy

2

102

ANSI

yy.mm.dd

3

103

British/French

dd/mm/yy

4

104

German

dd.mm.yy

5

105

Italian

dd-mm-yy

6

106

-

dd mon yy

7

107

-

Mon dd, yy

8

108

-

hh:mm:ss

-

9 or 109 (*)

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

-

13 or 113 (*)

Europe default + milliseconds

dd mon yyyy hh:mm:ss:mmm(24h)

14

114

-

hh:mi:ss:mmm(24h)

-

20 or 120 (*)

ODBC canonical

yyyy-mm-dd hh:mi:ss(24h)

-

21 or 121 (*)

ODBC canonical (with milliseconds)

yyyy-mm-dd hh:mi:ss.mmm(24h)

-

126(***)

ISO8601

yyyy-mm-dd Thh:mm:ss.mmm(no spaces)

-

130*

Hijri****

dd mon yyyy hh:mi:ss:mmmAM

-

131*

Hijri****

dd/mm/yy hh:mi:ss:mmmAM




This table shows the style values for float or real conversion to character data.

Value

Output

0 (default)

Six digits maximum. Use in scientific notation, when appropriate.

1

Always eight digits. Always use in scientific notation.

2

Always 16 digits. Always use in scientific notation.



In the following table, the column on the left represents the style value for money or smallmoneyconversion to character data.

Value

Output

0 (default)

No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.

1

Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.

2

No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.


Return type of CONVERT function is same as data_type parameter.




Examples of CONVERT Function :

Example 1 : Use of CONVERT function in select clause 

SELECT CONVERT (VARCHAR(10), 100.50)

Output
100.50

Above example converts an integer value 100.50 to varchar type.




Example 2 : Use of CONVERT function to convert table column value in select clause


SELECT ProductName, UnitPrice, CONVERT (INT,UnitPrice) AS ApproPice
FROM    Products

Output
ProductName                             UnitPrice    ApproPrice
Queso Manchego La Pastora         38.00          38
Konbu                                         6.00            6
Tofu                                           23.25          23
Genen Shouyu                             15.50          16
Pavlova                                       17.45          17
Alice Mutton                                39.00          39
Carnarvon Tigers                         62.50          63
Teatime Chocolate Biscuits             9.20            9
 

Above example returns converted unitprice in an integer value named as ApproPrice of table products.




Example 3 : Use of CONVERT function in where clause 


SELECT ProductName, UnitPrice 
FROM    Products
WHERE  CONVERT(INT,UnitPrice) = 10

Output
ProductName                                           UnitPrice
Aniseed Syrup                                          10.00
Sir Rodney's Scones                                 10.00
Jack's New England Clam Chowder               9.65
Rogede sild                                               9.50
Zaanse koeken                                           9.50
Longlife Tofu                                            10.00

Above example returns all products whose unit price is approximately or equal to $10.
  
Share: 

 
 
 

Didn't find what you were looking for? Find more on CONVERT Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of CONVERT Function is from United States.
 
View All Articles

 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!