Cast and Convert functions in SQL Server Part 28

Summary

In this SQL Server tutorial, the instructor Wenet explores the intricacies of data type conversion using CAST and CONVERT functions. The session clarifies when to use each function, highlighting that CAST adheres to ANSI standards, ensuring compatibility across databases, while CONVERT offers more flexibility, including the use of styles for formatting datetime. Practical examples demonstrate converting datetime to a string with specific formats and grouping data by date, emphasizing the importance of converting datetime to date when necessary. The tutorial also touches on concatenating data types and concludes with a comparison of CAST and CONVERT, recommending CAST for portability and CONVERT for its enhanced formatting capabilities.

Takeaways

• 😀 The session focuses on learning about the CAST and CONVERT functions in SQL Server, which are used to convert data from one type to another.
• 🔍 The CAST function is used to convert the data type of a column, such as changing the 'date of birth' from datetime to varchar for better display.
• 📊 The CONVERT function is similar to CAST but offers additional flexibility, including the ability to specify a 'style' parameter to control the format of the converted data.
• 📝 An example given is converting a datetime column to display the date in 'DD MM YYYY' format using the CONVERT function with style 103.
• ❗ It's important to note that the LENGTH parameter in CAST and CONVERT is optional and can be used to specify the length of the converted data.
• 📅 The CONVERT function is necessary when you want to control the formatting of date and time data types, which CAST does not support.
• 💡 SQL Server 2008 introduced the 'date' data type, allowing for easier extraction of date parts without converting to varchar, which was necessary in SQL Server 2005.
• 🔑 The script provides practical examples, such as concatenating an integer ID with a varchar name after converting the ID to varchar.
• 🧩 Grouping data by date requires converting the datetime data type to just the date part to aggregate correctly, which can be achieved using either CAST or CONVERT.
• 🌐 CAST is based on ANSI standards, making it more portable across different database applications, whereas CONVERT is specific to SQL Server and offers more functionality.

Q & A

What are the two functions in SQL Server used for data type conversion?

The two functions in SQL Server used for data type conversion are CAST and CONVERT.

What is the primary difference between the CAST and CONVERT functions?

The primary difference is that CONVERT has an optional style parameter, which CAST does not have. Additionally, CAST is based on ANSI standards, making it more portable across different database applications, while CONVERT is specific to SQL Server and offers more flexibility.

Why would you choose to use CAST over CONVERT in SQL Server?

You would choose to use CAST over CONVERT when portability is a concern, as CAST is based on ANSI standards and can be used across different database applications. It's also the choice when you do not need the style functionality provided by CONVERT.

How can you control the formatting of datetime data types in SQL Server?

You can control the formatting of datetime data types in SQL Server using the style parameter in the CONVERT function. This allows you to specify how the datetime should be displayed, for example, as 'DD MM YYYY' using style 103.

What is the purpose of the style parameter in the CONVERT function?

The style parameter in the CONVERT function is used to control the format of the output when converting datetime data types. It allows for various predefined formats to be applied to the converted data.

How can you obtain only the date part from a datetime data type in SQL Server?

To obtain only the date part from a datetime data type in SQL Server, you can use the CONVERT function with a style parameter that specifies only the date, such as style 1 ('YYYY-MM-DD') or style 101 ('YYYYMMDD'). Alternatively, you can convert the datetime to the 'date' data type using either CAST or CONVERT.

What is the syntax for using the CAST function in SQL Server?

The syntax for using the CAST function in SQL Server is: CAST (expression AS data_type [(length)]). The length is optional and depends on the target data type.

How do you concatenate a numeric column with a string column in SQL Server?

To concatenate a numeric column with a string column in SQL Server, you must first convert the numeric column to a string data type using either CAST or CONVERT, and then use the concatenation operator (+) to combine it with the string column.

What is the issue with grouping by a datetime column that includes both date and time in SQL Server?

Grouping by a datetime column that includes both date and time in SQL Server can result in no rows being grouped together because it's unlikely that multiple records will have the exact same date and time down to the second. To group by just the date, you should convert the datetime column to the 'date' data type using CAST or CONVERT.

How can you display a date in a custom format such as 'DD MM YYYY' in SQL Server?

You can display a date in a custom format such as 'DD MM YYYY' in SQL Server by using the CONVERT function with the appropriate style parameter, which in this case would be style 103.

Outlines

00:00

📘 Introduction to SQL Server's CAST and CONVERT Functions

This paragraph introduces the topic of the video, which is about the CAST and CONVERT functions in SQL Server. The speaker explains that these functions are used to convert data from one type to another and sets the stage for a detailed comparison between the two. An example is given involving a table with columns for ID, name, and date of birth, where the goal is to display the date of birth in a different format. The speaker demonstrates how both CAST and CONVERT can achieve the same outcome, converting the date of birth to a varchar format. The syntax for both functions is discussed, with an emphasis on the optional length parameter that can be used to specify the length of the converted data type.

05:00

🔢 Exploring the Style Parameter and Formatting Dates in SQL Server

In this paragraph, the focus shifts to the use of the style parameter in the CONVERT function, which is not available in the CAST function. The speaker illustrates how to format date and time data using different style numbers provided by Microsoft. An example is given where the date of birth is formatted to show only the date in a specific format (DD MM YYYY). The paragraph also touches on the importance of understanding the MSDN documentation, which lists various styles for different data types. The speaker emphasizes that while CAST and CONVERT can both convert data types, CONVERT offers more flexibility through the use of styles.

10:00

📅 Manipulating Date and Time Data in SQL Server Queries

The speaker continues the discussion on data type conversion by demonstrating how to extract just the date part from a datetime column. The paragraph explains that while the CONVERT function can be used with a style parameter to format the date, the CAST function cannot. It also introduces the 'date' data type introduced in SQL Server 2008, which allows for more straightforward extraction of the date part without converting to varchar. The paragraph includes a practical example of concatenating an integer ID with a varchar name, highlighting the need to convert the ID to varchar before concatenation.

15:02

📊 Grouping Data by Date and Understanding CAST vs CONVERT

This paragraph presents a practical scenario where the speaker wants to count the total registrations by date, ignoring the time part. The speaker shows how to use the CAST function to convert the datetime data to just the date data type for proper grouping. The paragraph concludes with a comparison between CAST and CONVERT, highlighting that CAST is based on ANSI standards and thus more portable across different database applications, while CONVERT is specific to SQL Server and offers more flexibility, including the use of styles for formatting.

Keywords

💡SQL Server

SQL Server is a relational database management system developed by Microsoft. It is used to store, manage, and retrieve data in a structured format. In the video, SQL Server is the platform on which the discussed functions and techniques are applied, specifically for data type conversion using CAST and CONVERT functions.

💡CAST function

The CAST function in SQL Server is used to convert a value from one data type to another. It is part of the ANSI SQL standard, making it a portable choice for database operations. In the video, the CAST function is demonstrated to convert the 'date of birth' column from a DATETIME data type to a VARCHAR data type, allowing for customized formatting.

💡CONVERT function

The CONVERT function is another tool in SQL Server for data type conversion, with the added flexibility of a 'style' parameter to control the format of the converted data. Unlike CAST, CONVERT is specific to SQL Server and offers more control over the output format, as shown in the video when converting dates to a specific string format.

💡Data type conversion

Data type conversion refers to the process of changing data from one format to another, which is a common task in database management. In the video, the concept is central as it discusses the use of CAST and CONVERT functions to convert data types, such as transforming DATETIME to VARCHAR for better presentation.

💡VARCHAR data type

VARCHAR is a variable-length character data type in SQL Server used to store strings. The video uses VARCHAR as a target data type when converting columns like 'date of birth' to display them in a more readable format, such as including month names and AM/PM indicators.

💡DATETIME data type

DATETIME is a data type in SQL Server that stores both date and time. The video script includes an example where DATETIME data is converted to a more user-friendly format using the CAST and CONVERT functions, highlighting the importance of data type conversion for reporting and user interface display.

💡Style parameter

The style parameter is an optional feature of the CONVERT function that allows for specific formatting of date and time data. The video explains how to use style parameters to control the output format of date conversions, such as displaying dates in 'DD MM YYYY' format, which is crucial for generating reports with a consistent look.

💡Group By

Group By is a clause in SQL used to group rows that have the same values in specified columns into summary rows, like 'find the number of customers in each country'. In the video, Group By is used to count total registrations by date, but requires the conversion of DATETIME to DATE to group by day only.

💡Concatenation

Concatenation in SQL is the process of joining two or more strings together. The video demonstrates concatenation when combining an ID (converted to VARCHAR) with a name to create a new column 'name-ID', illustrating the necessity of data type conversion for string operations.

💡MSDN documentation

MSDN (Microsoft Developer Network) documentation is a resource for developers to find technical information and guidelines for Microsoft technologies. The video references MSDN for details on style parameters and data type conversion, emphasizing the importance of consulting official documentation for accurate and detailed guidance.

Highlights

Introduction to SQL Server's cast and convert functions for data type conversion.

Demonstration of converting date of birth to a formatted string using cast and convert functions.

Explanation of the syntax and usage of the cast function with an example.

Discussion on the optional length parameter in cast and convert functions.

Example of specifying the length parameter to control the output format.

Introduction to the convert function and its syntax.

Explanation of the optional style parameter in the convert function.

Practical example of using the style parameter to format date and time.

Guidance on how to read MSDN documentation for optional parameters.

Illustration of converting datetime to just the date part using the convert function.

Introduction of SQL Server 2008's date data type and its use in conversion.

Example of concatenating integer and string data types using cast function.

Real-world example of aggregating registrations by date using group by clause.

Explanation of why grouping by datetime fails and the solution using the date data type.

Discussion on the differences between cast and convert functions.

Recommendation on when to use cast or convert based on portability and functionality needs.

Conclusion and resources for further learning on SQL Server and related topics.

Transcripts

hello welcome to preim Technologies I am

wenet this is part 28 of SQL server in

this session we'll learn about the cast

and convert functions when to choose one

over the

other now in SQL Server to convert one

data type to another we have two ways

cast and convert functions can be used

now before looking at the syntax of the

cast and convert functions let's look at

an example now if you look at this table

I have got three columns ID name and

date of birth now what I want to do is

in the output you know I want the output

like this the second table we have here

ID name date of birth as it is and a

converted date of birth now in the

output I want a three-letter month name

and an AM PM for the time instead of

displaying it like this I want you know

the date to be displayed as shown in the

converted date of birth column okay so

obviously in order to achieve that we

have to convert date time date of birth

column into nare care column and to do

that we can use so we are basically

converting one data type to another so

to do that we have cast and convert

functions now if you look at these two

queries here the first and second

queries these two queries give the same

output that you see on this second table

here okay so what are we doing in the

output we want ID name date of birth and

converted DB uh date of birth so four

columns so ID name and date of birth as

usual and I I want the converted date of

birth column so in order to convert the

date of birth I'm using the cast

function okay so cast and I'm saying C

con cast this date of birth to envir

care okay so if you look at the syntax

uh cast expression or column name as

your target data type our Target data

type here is nare care and if you look

at there we have another piece to it we

have have this length within square

brackets which means whatever is

specified in square brackets is optional

that's how we read mstn documentation if

they have specified anything within the

brackets then that means that part is

optional so we have't specified the

length here if you want to you can

specify the length let's look at that

quick example of how to specify the

length so if you look at this particular

example select star from TBL employees I

get ID name datea of birth now what we

want is we want to convert the date of

birth into n car column and to do that

we are using the cast function passing

in date of birth column and asking it to

convert to nare care okay and we are

giving it an alas name as convert as

converted date of birth so when I

execute that we should get the converted

date of birth okay now here for the

Target data type we are not specifying

the length if you wish you can specify

the length for example let us say if I

say the length is five convert the date

time date of birth to enare care of

length five so obviously when we execute

this query as you might expect you know

the output will be knocked off to Five

Characters okay so you may want to be

careful when you specify that length on

the other hand when I specify 10 you

know still something is knocked off so

maybe when I specify 11 we get

everything back but we don't get the

time so if you want time maybe you will

have to slightly increase that to maybe

20 yep we get the time as well okay now

so that's the Syntax for cast function

cast your Source column as Target data

type and length of the target data type

is optional okay and the same is the

case as convert function if you look at

the convert function you specify the

target data type first we want to

convert to nare comma and your Source

column which is nothing but our date of

birth column and if you look at the

syntax from MSD in here it says this is

the target data type and obviously

length is optional again because that's

present in the square brackets and your

Source column or expression and if you

look at this here we have another third

parameter for convert function in this

query we haven't used that but there is

another third optional parameter which

is the style parameter we'll talk about

that in a bit okay but understand that

there is this third parameter called

style for convert but cast doesn't have

that okay so obviously both of these

queries give us the same output as you

see here on this table Okay cool so

let's execute those two queries and make

sure we get the same

output okay so we get the same output

now let's look at this example let's see

how to use the style parameter um okay

so what I want to do is in this in the

first example that we have seen we are

converting the date of birth to nare

care but we don't really have have the

control on how we want this you know

date time to be

formatted okay but if you want to

control how that date time should be

formatted then msdn I mean Microsoft has

provideed us with a lot of

styles okay so for example I want you to

write a query now which gives me the

date of birth like this I just first

thing is I just want only the date and

that too the date should be in DD mm y y

y format okay so to do that we are using

the convert function you cannot do that

using cast function you'll have to use

convert function so I'm using the

convert function and I'm saying okay

convert the date of birth to nare care

and then I'm using the third optional

style parameter as well so for style

parameter this is the third parameter so

for the third parameter I'm using 103 so

what does 103 mean check this table 103

means TD mm y y y okay now do I need to

remember all these integer numbers now

not only these there are several of them

okay I just picked a few from the msdn

documentation there are literally you

know 20 to 30 Styles there on the msdn

website okay so we don't have to really

remember them if you just search Google

you know datetime Styles in SQL Server

you'll get that as the first article

okay so we are basically saying convert

this to ddmm y y y and we have the

output

okay so if you want to control how the

output date and time to be formatted you

can use Styles okay now not only date

and time even if you want to format

decimals the way you want it you can use

Styles okay so all the styles for all

the related data types are present on

msdn at this URL if you wish to check

them so so far what we have seen uh we

have understood that there are two ways

to convert from one data type to another

cast and convert what is the difference

between them convert has an optional

style parameter whereas cast doesn't

have it okay we'll look at more

differences between cast and convert in

a bit okay also we have seen how to read

the msdn documentation so according to

the msdn documentation if something is

in within the square brackets then that

piece is optional you may or may not

provide that just like length and style

parameters all right now let's look at

how to get just the date part in the

previous example we have seen how to get

just the date part you know we are

converting the date of birth to n care

and we are using 103 style not only 103

you can also use 101 or one2 depending

on which style you want all of them will

give you just the date so that's one way

convert it to nare care and specify the

style you want okay so that's one way

and that's exactly here that's one way

to get just the date part of a complete

date time okay

but in SQL Server 2008 we have this date

data type introduced in SQL Server 2005

we only have date time okay but in SQL

Server 2008 they have um date time date

as well as time three different

parameters um data type sorry um so

since we have a whole you know just the

date data type what you can do is

instead of converting a datetime data

type to an where care you can say okay I

will convert it to just date which will

give you the date part just the date

part okay and you can do that either

using cast or convert function okay but

to control the formatting of the date

part you still have to use you know you

still have to convert the date time to

nare care using the Styles provided when

converting to date data type the convert

function will ignore the St style

parameter okay the convert function will

respect the style parameter only if

you're converting it to Vare or nare you

know basically the to the string data

type all right so that's about how we

want to convert you know date time just

to date okay another simple example that

I want to show you is um look at this I

have the ID of the person and the name

of the person um I want the name- ID

okay so name name- ID both in the same

column so how do I do that obviously

since ID is integer and if you want to

concatenate that integer with the name

of the person obviously you will have to

convert that ID to nare care and that's

exactly what we are doing here select ID

name both the columns as usual and then

I'm saying select name to the name

concatenate hyphen I mean the dash and

to that concatenate ID

after converting it to andare Let's

quickly do that okay so what we want we

want select

ID comma

name um and I want

ID sorry name to the name we want to

concatenate

Dash and to that we want to concatenate

the ID ID from TBL employees table let's

execute this and see what happens

obviously we get an error conversion

failed when converting the nare care

value to data type integer and that

makes sense you're trying to concatenate

this integer to this

name you know which is nare care it's

not possible and you are you doing that

using the plus sign okay so that's why

you have to convert this to bar car you

can either use cast or convert I'm using

cast here cast ID as Nar

care and then when we execute this

obviously we'll get the output as

expected and if we want to give a column

name to that we use the alas as maybe

name-

ID so when we execute that we should get

the output that we are

expecting

okay so we'll look at another you know

real practical example and to understand

this better I have this table called TBL

registrations so let's look at that

select star from TBL registrations table

so if you check this table okay let's go

back to the slides if you check this

table there are users who are registered

and there is this registered date so if

you look at this one two you know there

are two people who have registered on

25th and there are 1 2 three people who

are registered on 24th and there's one

person who is registered on 26th now

what I want you to do is I want you to

write a query which will give me the

total registrations by the date I don't

really care at what time they have

registered but I want the total count of

registrations by the date okay so if you

look at this I can clearly say 24th you

know most registrations happened on 24th

3 that's the highest and least number of

registrations on 26th okay so obviously

if we have to do this we use Group by

class again if you're new to group by um

please check the earlier um you know

Parts in this video series so now let us

see how to write this query and

obviously if you look at this here it's

pretty simple in the output what you

want you want the registration date and

total registrations so we will select

the registration date okay let's first

write this

query okay so in the output we want the

registration date so what we need to do

is we need to select the registration

date so

registration date and what else we want

we want count

of

ID as let's say

total from which table we want that from

the registrations table so TBL

registrations and we want want to group

by registration date because we want to

count the total number of registrations

by date so we are grouping by

registration date so when I execute this

look at what's going to happen uh

invalid column name registration date

let's

see so what has gone wrong there

registered date not registration

registered

date

huh okay so let's execute that now if

you look at the output this is um this

is not what we expected

now why did we get this output because

you are saying Group by registered date

but within the date we have got date and

time as well at the same date and time

nobody has registered that's why it's

not grouping anybody it's in fact

grouping but there is but there is no

one who is registered on the same date

and same time okay so if you want to

group just by the date then you'll have

to convert this to date data type so

let's convert this to date data type and

to do that we can use cast register date

as we can use

the date data

type so we need to do the same thing

even in the select list because we don't

want the time we just want the date and

we want to group by that date okay so

when we execute that you should get the

output as expected and obviously we will

not have the column name if you want the

column name just give the

alas registration date so obviously when

we execute this we should get the output

as

expected okay so this query when you

have casted the date to I mean the date

time data type to date you are able to

group them correctly as you expect by

day otherwise we got the output as shown

here which is incorrect which is not

what we want

and finally we'll talk about the

differences between cast and convert

okay so you know if we want to convert

from one data type to another data type

we have two ways why do we have two ways

in the first place why do we have these

two different functions

Now cast is actually an NC standard okay

it's based on standards so obviously if

it's based on standards then that

function can be used on different

database

applications okay so if portability is a

concern and if you want to use the

script that you have written with other

database applications use cast because

it's based on ANC standard whereas

convert is specific to SQL Server okay

but the advantage of using convert is

that it provides more flexibility than

cast and we have seen examples where uh

we have actually controlled how we want

datetime data types to be converted

using styles with the convert function

okay with the cast function we cannot

use style parameter meter okay now when

do you when should you use one over the

other now the general rule is since cast

is based on ANC standard use cast okay

unless you want to take the advantage of

the style functionality in convert

function okay on this slide you can find

resources for asp.net and C interview

questions that's it for today thank you

for listening have a great day