Conversion Functions Using SQL Query in SQL Server

Now to look at how to use conversion functions in queries with the following:

1. where to use
2. when to use
3. which one is best to use which situation

Types of Conversions:

1. Cast
2. Convert

In general for reference:

• Value/Expression -> Input we are going to pass
• Data_Type -> System defined datatype like int, varchar, float, etc.

Cast and Convert

Syntax for CAST: CAST ( value/expression AS data_type [ ( length ) ] )
Syntax for CONVERT: CONVERT ( data_type [ ( length ) ] , value/expression [ , style ] )

Cast: Example

Select cast('12343.5787' as float) as [Float Value],
cast('12343.5787' as decimal) as [Decimal Value],
cast('12343.5787' as decimal(7,2)) as [Decimal Value with length],
-- with length
cast('01/13/2012' as datetime) as [Date Value]
-- The datetime input value format should be system's datetime format

Output for Cast function



Convert: Example

Select Convert(float, '12343.5787') as [Float Value],
convert(decimal, '12343.5787') as [Decimal Value],
convert(decimal(7,2), '12343.5787') as [Decimal Value with length], -          - with  length
convert(datetime,'01/13/2012') as [Date Value]
-- The datetime input value format should be system's datetime format

Output for Convert function



Situation of using CONVERT over CAST

CONVERT is specific to SQL Server, and allows for a greater breadth of flexibility when converting between date and time values, fractional numbers, and monetary signifiers.

Convert can be used to convert date and/or time to a specific format.

Example

Select CONVERT(datetime , '03/15/18', 1) as [dd/MM/yy],
CONVERT(datetime , '1/31/2012', 102) as [MM/dd/yyy]

Output



When to use CAST and Convert

For all the normal conversion, use CAST instead of Convert. Take CAST as first priority unless for the specific format like Date time, Money, fractional Conversion like below.

Select CONVERT(datetime , '03/15/18', 1) as [dd/MM/yy],
CONVERT(datetime , '1/31/2012', 102) as [MM/dd/yyy],
Convert(money,10.3496847)  as [Money]

Output



Datetime Convertion formats Example

-- Convert datetime to text style (format) list - SQL time format
-- SQL Server without century (YY) date styles (there are exceptions!)
-- Generally adding 100 to style number results in century format CCYY / YYYY
SELECT convert(varchar,getdate())    -- Mar 15 2018 10:35AM
SELECT convert(varchar,getdate(),0)  -- Mar 15 2018 10:35AM
SELECT convert(varchar,getdate(),1)  -- 03/15/18
SELECT convert(varchar,getdate(),2)  -- 18.03.15
SELECT convert(varchar,getdate(),3)  -- 15/03/18
SELECT convert(varchar,getdate(),4)  -- 15.03.18
SELECT convert(varchar,getdate(),5)  -- 15-03-18
SELECT convert(varchar,getdate(),6)  -- 15 Mar 18
SELECT convert(varchar,getdate(),7)  -- Mar 15, 18
SELECT convert(varchar,getdate(),8)  -- 10:39:39
SELECT convert(varchar,getdate(),9)  -- Mar 15 2018 10:39:48:373AM
SELECT convert(varchar,getdate(),10) -- 03-15-18
SELECT convert(varchar,getdate(),11) -- 18/03/15
SELECT convert(varchar,getdate(),15) -- 180315
SELECT convert(varchar,getdate(),13) -- 15 Mar 2018 10:41:07:590
SELECT convert(varchar,getdate(),14) -- 10:41:25:903
SELECT convert(varchar,getdate(),20) -- 2018-03-15 10:43:56
SELECT convert(varchar,getdate(),21) -- 2018-03-15 10:44:04.950
SELECT convert(varchar,getdate(),22) -- 03/15/18 10:44:50 AM
SELECT convert(varchar,getdate(),23) -- 2018-03-15
SELECT convert(varchar,getdate(),24) -- 10:45:45
SELECT convert(varchar,getdate(),25) -- 2018-03-15 10:46:11.263

-- T-SQL with century (YYYY or CCYY) datetime styles (formats)
SELECT convert(varchar, getdate(), 100) -- Oct 23 2016 10:22AM (or PM)
SELECT convert(varchar, getdate(), 101) -- 10/23/2016
SELECT convert(varchar, getdate(), 102) -- 2016.10.23
SELECT convert(varchar, getdate(), 103) -- 23/10/2016
SELECT convert(varchar, getdate(), 104) -- 23.10.2016
SELECT convert(varchar, getdate(), 105) -- 23-10-2016
SELECT convert(varchar, getdate(), 106) -- 23 Oct 2016
SELECT convert(varchar, getdate(), 107) -- Oct 23, 2016
SELECT convert(varchar, getdate(), 108) -- 09:10:34
SELECT convert(varchar, getdate(), 109) -- Oct 23 2016 11:10:33:993AM (or PM)
SELECT convert(varchar, getdate(), 110) -- 10-23-2016
SELECT convert(varchar, getdate(), 111) -- 2016/10/23
SELECT convert(varchar, getdate(), 112) -- 20161023
SELECT convert(varchar, getdate(), 113) -- 23 Oct 2016 06:10:55:383
SELECT convert(varchar, getdate(), 114) -- 06:10:55:383(24h)
SELECT convert(varchar, getdate(), 120) -- 2016-10-23 06:10:55(24h)
SELECT convert(varchar, getdate(), 121) -- 2016-10-23 06:10:55.383
SELECT convert(varchar, getdate(), 126) -- 2016-10-23T06:10:55.383
GO

The following illustration shows all explicit and implicit data type conversions that are allowed for SQL Server system-supplied data types.


Thanks for reading this article. Have a nice day.