Convert SQL | Convert Function in SQL - sql - sql tutorial - learn 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.
- The cast and convert functions provide similar functionality.
Syntax:
The syntax for the CONVERT function in SQL Server (Transact-SQL) is:
- Below is the table, the two columns on the left represent the style values for datetime or small date time 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 mo n yyyy hh:mi:ss:mmmAM |
- | 131* | Hijri*** * | dd/mm /yy hh:mi:ss:mmmAM |
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialExamples of CONVERT Function :
Examples of CONVERT Function :
Example 1: Use of CONVERT function in select clause
Output
- Above example converts an integer value 100.50 to varchar type.
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialExample 2: CONVERT function in Oracle:
Example 2: CONVERT function in Oracle:
- In Oracle, the CONVERT function is used differently. It converts a string from one character set to another.
Syntax in Oracle
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialExample 3: We use the following table for our example.
Example 3: We use the following table for our example.
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialTable Student_Score
Table Student_Score
Column Name | Data Type |
---|---|
StudentID | integer |
First_Name | char(20) |
Score | float |
This table contains the following rows:
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialTable Student_Score
Table Student_Score
StudentID | First_Name | Score |
---|---|---|
1 | Jenny | 85.2 |
2 | Bob | 92.5 |
3 | Alice | 90 |
4 | James | 120.1 |
- The SQL statement,
produces the following result set:
First_Name | Int_Score |
---|---|
Jenny | 85 |
Bob | 92 |
Alice | 90 |
James | 120 |