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.