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

convert to int in sqlconverter data sqlhow to convert varchar to datetime in sqlsql convert to integersql convert syntaxsql convert datetime to varcharconvert to varchar in sqlhow to convert string to date in sqlconvert string to datetime sql serversql convert decimal to stringconvert to integer sqlconvert datetime to varchar in sqlsql server convert datetime to varcharsql convert string to decimalconvert to decimal in sqlsql convert number to dateconvert date format in sqlconvert string to datetime in sqlcast and convert in sqlsql convert char to intconvert time sql serverconvert char to int sqldatetime convert sqlsql convert datetime formatsql convert decimalconvert string to number sqlconvert number to string sqlsql convert string to timeconvert sql date to stringconvert to int sql serversql convert numeric to stringsql convert float to intconvert date to varchar in sqlconvert int to string in sqlconvert datetime to string sql serverconvert to string in sqlconvert datetime to string in sqlsql convert datetime to stringsql cast vs convertconvert int to varchar sql serverhow to convert varchar to int in sqlconvert decimal sqlconvert datetime to date in sql serversql server convert string to datetimehow to convert date format in sqlconvert date in sqlsql convert to decimalhow to convert datetime to date in sqlsql server convert datetime to stringconvert datetime to date sqlconvert datetime in sqlconvert varchar to int in sql serverconvert varchar to int in sqlsql convert to datetimeconvert to datetime sqlconvert datetime to string sqlconvert in sql server 2012convert to float sqlsql datetime convertconvert varchar to date in sqlsql convert string to datetimeconvert function in sql serversql date convert formats
Connection failed: SQLSTATE[HY000] [1045] Access denied for user 'wikitechy_frhvdxx67'@'localhost' (using password: YES)