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:

CONVERT(data_type(length),expression,style)
  • 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 tutorial

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.
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

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

CONVERT (string, [new character set], [original character set])
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Example 3: We use the following table for our example.

Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

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 tutorial

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,
SELECT First_Name, CONVERT(Score, Integer) Int_Score FROM Student_Score;

produces the following result set:

First_Name Int_Score
Jenny 85
Bob 92
Alice 90
James 120

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to Convert SQL | Convert Function in SQL