In this article we will understand case and convert function in sql server in detail and what is the difference between case and convert function in sql server.
Previous SQL Articles :- https://sagarjaybhay.com/category/sql-server/
When we want to convert from one data-type to another data-type we have Case and Convert function In SQL server.
Cast function in SQL Server
This Cast function can convert an expression to the provided datatype. In short, it is used to convert one data type to another data-type.
CAST(expression AS datatype [(length)]) Or CAST(source-column-name AS target-datatype[(length-optional)])
In the above function, we will understand each part
- Expression: It is the required part and it means value to convert.
- Datatype: This is also must be required and using this means you want to convert expression value to this datatype.
- Length: when you see anything in the bracket as per Microsoft guideline it is optional means if you want to provide then write else we are ok with not provide anything for this.
For implicit and explicit conversion of a chart you can find below link:
Query and data to create a table and add data.
create table studentTable ( id INT, first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(50), gender VARCHAR(50), DateOfBirth nvarchar(40) ); insert into studentTable (id, first_name, last_name, email, gender, DateOfBirth) values (1, 'Celestia', 'Dufall', 'email@example.com', 'Female', '2019-03-20 14:25:16'); insert into studentTable (id, first_name, last_name, email, gender, DateOfBirth) values (2, 'Janek', 'Duignan', 'firstname.lastname@example.org', 'Male', '2019-08-30 05:59:58'); insert into studentTable (id, first_name, last_name, email, gender, DateOfBirth) values (3, 'Bartel', 'Farriar', 'email@example.com', 'Male', '2019-01-04 04:36:17'); insert into studentTable (id, first_name, last_name, email, gender, DateOfBirth) values (4, 'Nobe', 'Lawther', 'firstname.lastname@example.org', 'Male', '2019-02-16 15:31:08'); insert into studentTable (id, first_name, last_name, email, gender, DateOfBirth) values (5, 'Marquita', 'Membry', 'email@example.com', 'Female', '2019-07-29 03:10:39');
Now we can see the result of how to cast function is applied.
select id, first_name, last_name,CAST(DateOfBirth as date) as DateOfBirth from studentTable;
In the above query, we use CAST(DateOfBirth as date) is cast function in that DateOfBirth is a column in studenttable and date is datatype we want to convert.
Convert Function In SQL Server
It will convert the value of any type into a given or specified data-type.
CONVERT(data_type(length), expression, style)
- Data-type: It is a required field and it will convert your expression i.e value of some data-type to your mentioned data-type.
- Length:- it optional parameter.
- Expression:- It is an actual value to convert another data-type.
- Style:- This optional parameter is used if you want to format data as per your need you can use this style option. By using style option you can control the display formatting. For more formatting style need to check this link. https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15
select id, first_name, last_name,Convert(date, DateOfBirth) as DateOfBirth from studentTable;
Difference between Cast and Convert function in the SQL server?
- CAST is based on ANSI standard and Convert is specific to the SQL server. So if you have a script and want to run on different types of a database then you can use CAST. Means if you have portability concern you can use CAST.
- Convert function provides more flexibility than the CAST function means you can format the output more controlled way than CAST function.