Summary -

In this topic, we described about the Conversion Functions with detailed example.

Conversion Functions are used to convert one datatype of data into another datatype.

Conversion Functions are two types - CAST and CONVERT. The difference between these two are just a format. In CONVERT statement we can convert data into another datatype with our required format.

Syntax for CAST – 

CAST (expression AS datatype [length])
  • expression – It represents the data which we want to convert.
  • datatype – the datatype which we want to convert the value.
  • length – The length of the data which is optional.

Syntax for CONVERT – 

CONVERT (datatype [length] expression, style)
  • datatype – the datatype which we want to convert the value.
  • length – The length of the data which is optional.
  • expression – It represents the data which we want to convert.
  • style – It represents the output style of converted datatype.

The different styles which are used in CONVERT statement to convert data/time into character data was listed as follows –

Without century With century Output Standard
0 100 mon dd yyyy hh:miAM/PM Default
1 101 mm/dd/yyyy US
2 102 yyyy.mm.dd ANSI
3 103 dd/mm/yyyy British/French
4 104 dd.mm.yyyy German
5 105 dd-mm-yyyy Italian
6 106 dd mon yyyy -
7 107 Mon dd, yyyy -
8 108 hh:mm:ss -
9 109 mon dd yyyy hh:mi:ss:mmmAM (or PM) Default + millisec
10 110 mm-dd-yyyy USA
11 111 yyyy/mm/dd Japan
12 112 yyyymmdd ISO
13 113 dd mon yyyy hh:mi:ss:mmm Europe (24 hour clock)>
14 114 hh:mi:ss:mmm 24 hour clock
20 120 yyyy-mm-dd hh:mi:ss ODBC canonical (24 hour clock)
21 121 yyyy-mm-dd hh:mi:ss.mmm ODBC canonical (24 hour clock)
126 yyyy-mm-ddThh:mi:ss.mmm ISO8601
127 yyyy-mm-ddThh:mi:ss.mmmZ ISO8601 (with time zone Z)
130 dd mon yyyy hh:mi:ss:mmmAM Hijiri
131 dd/mm/yy hh:mi:ss:mmmAM Hijiri

Converting money to VARCHAR in the following styles -

Value Explanation Output
0 No comma delimiters, 2 digits to the right of decimal 1234.56
1 Comma delimiters, 2 digits to the right of decimal 1,234.56
2 No comma delimiters, 4 digits to the right of decimal 1234.5678

Converting FLOAT to textual data in the following styles -

Value Explanation
0 maximum of 6 digits. Used in scientific notation, when appropriate.
1 8 digits. Always used in scientific notation.
2 16 digits. Always used in scientific notation.

Scenario – Converting FLOAT datatype to INT.

Requirement – Converting 26.98 as INTERGER type. The query was as follows – 

SELECT CAST (26.98 AS INT ) AS ConvertedValue;

By executing above query, we can get the output as shown in below –

ConvertedValue
26

Note - While converting FLOAT data to INT data, CAST will truncate the data.

Scenario – Converting DATE datatype to DATETIME datatype.

Requirement – Converting DATE datatype to DATETIME datatype. The query was as follows – 

SELECT CAST('2020-09-11' AS DATETIME) AS ConvertedValue;

By executing above query, we can get the output as shown in below –

ConvertedValue
2020-09-11 00:00:00.000

Scenario – Converting FLOAT datatype to STRING.

Requirement – Converting 26.98 as VARCHAR type. The query was as follows – 

SELECT CAST (26.98 AS VARCHAR ) AS ConvertedValue;

By executing above query, we can get the output as shown in below –

ConvertedValue
26.98

Scenario – Converting GETDATE( ) function into character datatype in result set by using CONVERT statement.

Requirement - Converting GETDATE( ) function into character datatype of different styles in result set without getting centuries by using CONVERT statement. The queries are as follows – 

SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 0) AS ConvertedFormat
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 1) AS ConvertedFormat
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 2) AS ConvertedFormat
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 3) AS ConvertedFormat
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 4) AS ConvertedFormat
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 5) AS ConvertedFormat
UNION ALL 
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 6) AS ConvertedFormat
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 7) AS ConvertedFormat
UNION ALL 
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 8) AS ConvertedFormat
UNION ALL 
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 9) AS ConvertedFormat
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 10) AS ConvertedFormat 
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 11) AS ConvertedFormat
UNION ALL 
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 12) AS ConvertedFormat 
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 13) AS ConvertedFormat 
UNION ALL 
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 14) AS ConvertedFormat 
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 20) AS ConvertedFormat 
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 21) AS ConvertedFormat;

By executing above query we can get output like as shown in below –

ConvertedFormat
Sep 12 2020 9:56AM
09/12/20
20.09.12
12/09/20
12.09.20
12-09-20
12 Sep 20
Sep 12, 20
09:56:38
Sep 12 2020 9:56:38:323AM
09-12-20
20/09/12
200912
12 Sep 2020 09:56:38:323
09:56:38:323
2020-09-12 09:56:38
2020-09-12 09:56:38.323

Requirement1 - Converting GETDATE( ) function into character datatype of different styles in result set with centuries by using CONVERT statement. The queries are as follows – 

SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 100) AS ConvertedFormat
UNION ALL 
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 101) AS ConvertedFormat 
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 102) AS ConvertedFormat
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 103) AS ConvertedFormat
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 104) AS ConvertedFormat
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 105) AS ConvertedFormat
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 106) AS ConvertedFormat
UNION ALL 
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 107) AS ConvertedFormat
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 108) AS ConvertedFormat
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 109) AS ConvertedFormat
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 110) AS ConvertedFormat 
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 111) AS ConvertedFormat
UNION ALL 
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 112) AS ConvertedFormat
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 113) AS ConvertedFormat 
UNION ALL 
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 114) AS ConvertedFormat
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 120) AS ConvertedFormat
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 121) AS ConvertedFormat
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 126) AS ConvertedFormat 
UNION ALL
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 127) AS ConvertedFormat
UNION ALL 
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 130) AS ConvertedFormat
UNION ALL 
SELECT CONVERT ( VARCHAR (100), GETDATE ( ), 131) AS ConvertedFormat;

By executing above query we can get output with century like as shown in below -

ConvertedFormat
Sep 12 2020 10:11AM
09/12/2020
2020.09.12
12/09/2020
12.09.2020
12-09-2020
Sep 12, 2020
12 Sep 2020
10:11:08
Sep 12 2020 10:11:08:003AM
09-12-2020
2020/09/12
20200912
12 Sep 2020 10:11:08:003
10:11:08:003
2020-09-12 10:11:08
2020-09-12 10:11:08.003
2020-09-12T10:11:08.003
2020-09-12T10:11:08.003
25 ???? 1442 10:22:50:833AM
25/01/1442 10:11:08:003AM

Scenario – Converting one datatype to another datatype.

Requirement – Converting FLOAT datatype into INT datatype. The query was as follows – 

SELECT CONVERT ( INT , 25.369) AS ConvertedFormat;

By executing above query, we can convert 25.369 FLOAT value as INT type of 25. The output was as follows -

ConvertedFormat
25

Scenario – Converting money to VARCHAR datatype.

Requirement - Converting money values into VARCHAR datatype in different styles by using CONVERT datatype. The query was as follows – 

SELECT CONVERT(Varchar,$7984.256,0) AS ConvertedFormat,
   CONVERT(Varchar,$7984.256,1) AS ConvertedFormat1,
   CONVERT(Varchar,$7984.256,2) AS ConvertedFormat2;

By executing above query we can get different styles of money value in VARCHAR datatype. The output was as shown in below –

ConvertedFormat ConvertedFormat1 ConvertedFormat2
7984.26 7,984.26 7984.2560

Scenario – Converting FLOAT values in the textual data format.

Requirement – Converting FLOAT type data to textual data in different formats. The query was as follows – 

DECLARE @variable FLOAT = 123987456321459753.23
SELECT CONVERT (VARCHAR (100), @variable, 0) AS Format,
SELECT CONVERT (VARCHAR (100), @variable, 1) AS Format1,
SELECT CONVERT (VARCHAR (100), @variable, 0) AS Format2;

By executing above query, we can get output like as shown in below -

Format Format1 Format2
1.23987e+017 1.2398746e+017 1.239874563214598e+017