Cast and Convert functions in SQL Server Part 28 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to presume technologies I
am blanket this is part 28 of signal
server in this session we'll learn about
the caste and convert functions when to
choose one over the other now in sequel
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 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 they
converted date of birth now in the
output I want a three-letter month name
and an ampm 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 n where 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 on 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
converter DB or date of birth so four
columns so ID name and date of birth as
usual and I want the converted date of
birth column so in order to convert the
date of birth I'm using the cached
function okay so cast and I'm saying cap
and cast this date of birth to n VAR cab
okay so if you look at the syntax cast
expression or column name as your target
data type our target data type here is n
where cap and if you look at there we
have another piece to it we have this
length within square brackets which
means whatever it is specified in square
brackets is optional that's how we read
MSD and documentation if they have
specified anything within the brackets
then
that means that part is optional so we
haven'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 date of
birth now what we want is we want to
convert the date of birth into n wear
care column and to do that we are using
the cast function passing in date of
birth column and asking it to convert to
n where care ok and we are giving it an
alias name as convert as converted date
of birth so when I execute that we
should get the converted date of birth
ok now here for the target data type we
are not specifying the length if you
wish you can specify the land for
example let us say if I say the length
as 5 convert the date time date of birth
to n where care of length 5 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 ten 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 cases convert function
if you look at the convert function you
specify the target data type first we
want to convert to n where care comma
and your source column which is nothing
but our date of birth column and if you
look at the syntax from MSDN 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 will talk about that
in a bit okay but understand that there
is this third parameter called style for
convert
but cash 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
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 n
where cap but we don't really have
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 provided 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 to that date should be in DD mmm
yyy format okay so to do that we are
using the convert function you cannot do
that using cast function you will have
to use convert function so I'm using the
convert function I am saying okay
convert rate of birth tree and we're
care and then I am 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 1 0 3
so what does 1 0 3 mean check this table
1 0 3 means DD mmm
yyy okay now do I need to remember all
these integer numbers now not only this
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 ok so
we don't have to really remember them if
you just search Google you know
date/time styles in sequel server you
will get that as the first article ok so
we are basically saying convert this to
DD mm yyy 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
to format decimals the way you want that
you can do 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 we have understood that there are
two ways to convert from one data type
to another caste and convert what is the
difference between them convert has an
optional style parameter whereas caste
doesn't have it okay we'll look at more
differences between caste and convert in
a bit okay also we have seen how to read
the MSDN documentation so according to
the MSDN documentation if something
isn't 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 path in
the previous example we have seen how to
get just the date part you know we are
converting the date of birth to an wear
care and we are using 1:03 style not
only 1:03 you can also use 1 0 1 or 1 0
2 depending on which style you want all
of them will give you just the date so
that's one way convert it to n where
care and specify the style you want ok
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 ok but in
sequence there were 2008 we have this
date datatype introduced in sequence
over 2005 we only have date time ok
but in sequel server 2008 they have day
time date as well as time 3 different
parameters of data type sorry so since
we have a whole you know just the date
data type what you can do is instead of
converting a date time data type to n
where care you can say ok I will convert
it to just date which will give you the
date part just the date part ok and you
can do that either using cast or convert
function ok 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 n where care
using the styles
provided when converting to date
datatype the convert function will
ignore the style parameter okay
the convert function will respect the
style parameter only if you are
converting it to where care or n where
can you know basically that to the
string datatype 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 look at this I have the ID of the
person in the name of the person I want
the name - ID okay
so 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 n where cat and that's exactly
what we are doing here select ID name
both the columns as usual and then I am
saying select name to the name
concatenate - I mean the - and - that
concatenate ID after converting it to
that and back cap let's quickly do that
okay
so what we want we want select ID comma
name and I want ID sorry name - the name
we want to concatenate - and to that we
want to concatenate the ID from TBL
employees table let's execute this and
see what happens obviously we get an
error conversion failed when converting
the end where care value to data type
integer and that makes sense you're
trying to concatenate this integer to
this name you know which is n well 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 bark at
you can either use cast or convert I'm
using cast here cast ID
and bear cab 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 alias as maybe
name - ID so when we execute that we
should get the output that we are
expecting okay so we 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 let's go back to the slides if you
check this table there are users who are
registered and there is this register
date so if you look at this one - you
know there are two people who have
registered on 25th and there are one two
three people who are registered on 24
and there's one person who is registered
on 26 now what he 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
24 3 that's the highest and least number
of registrations on 26th ok so obviously
if we have to do this we use group by
class again if you are new to group by
please check the earlier 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 ok 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 off
I D as let's say total from which table
we want that from the registration
stable so TBL registrations and we 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
invalid column name registration date
let's see so what has gone wrong there
register date not registration
registered a date
huh
okay so let's execute that now if you
look at the output this is this is not
what we expected now why did we get this
output because you are saying group by
register date but within the date we
have caught 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
will have to convert this to date
datatype so let's convert this to date
datatype and to do that we can use caste
register date as we can use the date
datatype 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 alias 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 datatype to date you
are able to group them correctly as you
expect by day otherwise because the
output as shown here which is incorrect
which is not what we want and finally we
will talk about the differences between
caste 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 caste is actually an ANSI
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 caste
because it's based on ANSI standard
whereas convert is specific to sequel
server okay but the advantage of using
convert is that it provides more
flexibility than cast and we have seen
examples where we have actually
controlled how we want date/time
datatypes to be converted using styles
with the convert function okay with the
cast function we cannot use style
parameter okay now when do you when
should you use one over the other now
the general rule is since cast is based
on ANSI standard use cast okay unless
you want to take the advantage of the
style functionality and convert function
okay on this slide you can find
resources for asp.net and C sharp
interview questions that's it for today
thank you for listening have a great day