Conversion Functions Using SQL Query in SQL Server

Parthiban Selvaraj
2y
37.5k
0
2

100

Article

Introduction

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

where to use
when to use
which one is best to use in which situation

Types of Conversions

Cast
Convert

For reference

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

Cast and Convert

The syntax for CAST - CAST ( value/expression AS data_type [ ( length ) ] )

The 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

The situation of using CONVERT over CAST

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

Convert can convert date and 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 average conversions, use CAST instead of Convert. Take CAST as a priority unless for the specific format like Date, Time, Money, or 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 Conversion 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 allowed for SQL Server system-supplied data types.

Summary

This article taught us about Conversion Functions Using SQL Queries in SQL Server. Thanks for reading this article. Have a nice day.