You are here

Data Type Conversion in SQL

sql

DATA TYPE CONVERSION

In this post we will discuss about data type conversion in SQL server 2016.

  • We have to make sure that when we indicate a literal of a type, we use the correct form. For example, literal of regular character string are delimited with single quotation marks, as ‘abc’, whereas literal of Unicode character strings are delimited with a capital N, and then single quotation marks, as in N’abc’.
  • When an expression has different types, SQL server needs to apply implicit conversion when possible, this is not possible every time we have to apply explicit conversion.
  • Explicit conversion is perform using CAST, CONVERT, PARSE, or TRY_CAST, TRY_CONVERT, TRY_PARSE.
  • When using expressions that involve operands of different types, SQL server usually converts the one that has the lower data type precedence to the one with the higher.

CAST()

  • Use to convert one data type to another. It gives error if conversion is not possible.

Syntax:
CAST(col_name AS datatype(size))
Here, datatype is target datatype
Example:
Declare @pid as int=1;
select CAST(@pid as varchar(50))as pid;
output:

pid

1

TRY_CAST()

  • It returns a value cast to the specified data type if cast succeeds, otherwise returns null.

Syntax:
TRY_CAST(col_name AS datatype(size))
Example:
Declare @name varchar(50)=’abc’;
select TRY_CAST(@name as int) as name;
output:

name

NULL

CONVERT()

  • The CONVERT() function has three parameters, the third parameter represents the style for the conversion, which is supported for character string and date and time.

Syntax:

CONVERT(datatype(size), col_name, style)

Styleid

Style format

100

Mm dd yyyy

101

Mm/dd/yyy
102

yy.mm.dd

103

dd/mm/yy

104

dd.mm.yy

105

dd-mm-yy

106

dd mm yy
107

mm dd,yy

108

hh:mm:ss

109

mm dd yyyy hh:mi:ss:mmm am(or pm)

110

mm-dd-yy

111

yy/mm/dd

112

yymmdd
113

dd mm yyyy hh:mm:ss:mmm(24h)

114

hh:mi:ss:mmm(24h)

120 or 20

yyyy-mm-dd hh:mi:ss(24h)

121 or 21

yyyy-mm-dd hh:mi:ss.mmm(24h)

126

yyyy-mm-dd hh:mm:ss:mmm
130

dd mon yyyy hh:mi:ss:mmmAm

131

dd/mm/yy hh:mi:ss:mmmAm

Example:

declare @odate date=’2016-02-12′;

select CONVERT(nvarchar(10),@odate,110) as orderdate;

output:

orderdate

02-12-2016

TRY_CONVERT()

  • This function is similar to convert function, but if CONVERT function fails to convert the value to requested type it returns error while TRY_CONVERT returns NULL value.

Syntax:
TRY_CONVERT(datatype, col_name)
Example:
select TRY_CONVERT(datetime,’02/30/2017′)as fails;
output:

fails

NULL

PARSE()

  • Returns the result of an expression, translated to the requested data type in SQL server.

Syntax:
 PARSE(string_value AS datatype)
Example:
Select PARSE(‘1000’ as int) as string;
Output:

string

1000

TRY_PARSE()

  • If PARSE function fails to convert the value throw an exception where TRY_PARSE returns a NULL value.

Syntax: 
TRY_PARSE(string_value AS datatype)
Example:
Select TRY_PARSE(‘2000/03/04’ as smallint) as type;
Output:

type

Null

Keep Visiting TechwithR.com for more upcoming top technical articles and also see my new blog Mag4info. and for kotlin tutorial also see my blog kotlin category.

You can also by SQL 70-761 book write by me from Amazon.

You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

Leave a Reply

Top