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

Adblocker detected! Please consider reading this notice.

We've detected that you are using AdBlock Plus or some other adblocking software which is preventing the page from fully loading.

We don't have any banner, Flash, animation, obnoxious sound, or popup ad. We do not implement these annoying types of ads!

We need money to operate the site, and almost all of it comes from our online advertising.

Please add wikitechy.com to your ad blocking whitelist or disable your adblocking software.

×