YT YouTube2Text

YouTube2Text → Transcripts → Cast and Convert functions in SQL Server Part 28

Cast and Convert functions in SQL Server Part 28 — Transcript

by kudvenkat · 2,817 words · 389 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00hello welcome to preim Technologies I am
0:02wenet this is part 28 of SQL server in
0:05this session we'll learn about the cast
0:07and convert functions when to choose one
0:09over the
0:11other now in SQL Server to convert one
0:14data type to another we have two ways
0:17cast and convert functions can be used
0:20now before looking at the syntax of the
0:22cast and convert functions let's look at
0:24an example now if you look at this table
0:26I have got three columns ID name and
0:28date of birth now what I want to do is
0:31in the output you know I want the output
0:33like this the second table we have here
0:35ID name date of birth as it is and a
0:38converted date of birth now in the
0:40output I want a three-letter month name
0:43and an AM PM for the time instead of
0:46displaying it like this I want you know
0:49the date to be displayed as shown in the
0:51converted date of birth column okay so
0:54obviously in order to achieve that we
0:56have to convert date time date of birth
1:00column into nare care column and to do
1:04that we can use so we are basically
1:06converting one data type to another so
1:08to do that we have cast and convert
1:10functions now if you look at these two
1:13queries here the first and second
1:14queries these two queries give the same
1:17output that you see on this second table
1:19here okay so what are we doing in the
1:21output we want ID name date of birth and
1:24converted DB uh date of birth so four
1:26columns so ID name and date of birth as
1:29usual and I I want the converted date of
1:31birth column so in order to convert the
1:34date of birth I'm using the cast
1:36function okay so cast and I'm saying C
1:40con cast this date of birth to envir
1:44care okay so if you look at the syntax
1:47uh cast expression or column name as
1:52your target data type our Target data
1:54type here is nare care and if you look
1:57at there we have another piece to it we
1:59have have this length within square
2:02brackets which means whatever is
2:04specified in square brackets is optional
2:07that's how we read mstn documentation if
2:10they have specified anything within the
2:12brackets then that means that part is
2:14optional so we have't specified the
2:17length here if you want to you can
2:18specify the length let's look at that
2:20quick example of how to specify the
2:22length so if you look at this particular
2:24example select star from TBL employees I
2:27get ID name datea of birth now what we
2:29want is we want to convert the date of
2:31birth into n car column and to do that
2:33we are using the cast function passing
2:35in date of birth column and asking it to
2:38convert to nare care okay and we are
2:40giving it an alas name as convert as
2:43converted date of birth so when I
2:45execute that we should get the converted
2:47date of birth okay now here for the
2:50Target data type we are not specifying
2:52the length if you wish you can specify
2:55the length for example let us say if I
2:57say the length is five convert the date
3:00time date of birth to enare care of
3:02length five so obviously when we execute
3:04this query as you might expect you know
3:07the output will be knocked off to Five
3:10Characters okay so you may want to be
3:12careful when you specify that length on
3:14the other hand when I specify 10 you
3:16know still something is knocked off so
3:19maybe when I specify 11 we get
3:21everything back but we don't get the
3:24time so if you want time maybe you will
3:26have to slightly increase that to maybe
3:2920 yep we get the time as well okay now
3:33so that's the Syntax for cast function
3:36cast your Source column as Target data
3:39type and length of the target data type
3:42is optional okay and the same is the
3:45case as convert function if you look at
3:46the convert function you specify the
3:48target data type first we want to
3:50convert to nare comma and your Source
3:54column which is nothing but our date of
3:56birth column and if you look at the
3:58syntax from MSD in here it says this is
4:01the target data type and obviously
4:03length is optional again because that's
4:05present in the square brackets and your
4:07Source column or expression and if you
4:09look at this here we have another third
4:11parameter for convert function in this
4:14query we haven't used that but there is
4:16another third optional parameter which
4:18is the style parameter we'll talk about
4:20that in a bit okay but understand that
4:23there is this third parameter called
4:25style for convert but cast doesn't have
4:27that okay so obviously both of these
4:30queries give us the same output as you
4:32see here on this table Okay cool so
4:36let's execute those two queries and make
4:38sure we get the same
4:40output okay so we get the same output
4:43now let's look at this example let's see
4:45how to use the style parameter um okay
4:49so what I want to do is in this in the
4:52first example that we have seen we are
4:54converting the date of birth to nare
4:58care but we don't really have have the
5:00control on how we want this you know
5:02date time to be
5:04formatted okay but if you want to
5:06control how that date time should be
5:09formatted then msdn I mean Microsoft has
5:12provideed us with a lot of
5:14styles okay so for example I want you to
5:17write a query now which gives me the
5:20date of birth like this I just first
5:22thing is I just want only the date and
5:25that too the date should be in DD mm y y
5:30y format okay so to do that we are using
5:34the convert function you cannot do that
5:35using cast function you'll have to use
5:38convert function so I'm using the
5:40convert function and I'm saying okay
5:41convert the date of birth to nare care
5:44and then I'm using the third optional
5:46style parameter as well so for style
5:49parameter this is the third parameter so
5:51for the third parameter I'm using 103 so
5:55what does 103 mean check this table 103
5:58means TD mm y y y okay now do I need to
6:03remember all these integer numbers now
6:05not only these there are several of them
6:08okay I just picked a few from the msdn
6:10documentation there are literally you
6:12know 20 to 30 Styles there on the msdn
6:14website okay so we don't have to really
6:16remember them if you just search Google
6:19you know datetime Styles in SQL Server
6:21you'll get that as the first article
6:24okay so we are basically saying convert
6:26this to ddmm y y y and we have the
6:28output
6:30okay so if you want to control how the
6:32output date and time to be formatted you
6:34can use Styles okay now not only date
6:38and time even if you want to format
6:39decimals the way you want it you can use
6:41Styles okay so all the styles for all
6:44the related data types are present on
6:47msdn at this URL if you wish to check
6:49them so so far what we have seen uh we
6:52have understood that there are two ways
6:54to convert from one data type to another
6:56cast and convert what is the difference
6:58between them convert has an optional
7:01style parameter whereas cast doesn't
7:03have it okay we'll look at more
7:05differences between cast and convert in
7:07a bit okay also we have seen how to read
7:10the msdn documentation so according to
7:12the msdn documentation if something is
7:14in within the square brackets then that
7:17piece is optional you may or may not
7:18provide that just like length and style
7:24parameters all right now let's look at
7:27how to get just the date part in the
7:29previous example we have seen how to get
7:31just the date part you know we are
7:33converting the date of birth to n care
7:36and we are using 103 style not only 103
7:39you can also use 101 or one2 depending
7:42on which style you want all of them will
7:44give you just the date so that's one way
7:46convert it to nare care and specify the
7:49style you want okay so that's one way
7:52and that's exactly here that's one way
7:54to get just the date part of a complete
7:57date time okay
8:00but in SQL Server 2008 we have this date
8:03data type introduced in SQL Server 2005
8:07we only have date time okay but in SQL
8:10Server 2008 they have um date time date
8:14as well as time three different
8:16parameters um data type sorry um so
8:19since we have a whole you know just the
8:22date data type what you can do is
8:24instead of converting a datetime data
8:27type to an where care you can say okay I
8:31will convert it to just date which will
8:34give you the date part just the date
8:36part okay and you can do that either
8:39using cast or convert function okay but
8:43to control the formatting of the date
8:45part you still have to use you know you
8:48still have to convert the date time to
8:51nare care using the Styles provided when
8:54converting to date data type the convert
8:57function will ignore the St style
9:00parameter okay the convert function will
9:02respect the style parameter only if
9:04you're converting it to Vare or nare you
9:07know basically the to the string data
9:10type all right so that's about how we
9:12want to convert you know date time just
9:15to date okay another simple example that
9:18I want to show you is um look at this I
9:21have the ID of the person and the name
9:22of the person um I want the name- ID
9:28okay so name name- ID both in the same
9:31column so how do I do that obviously
9:34since ID is integer and if you want to
9:36concatenate that integer with the name
9:39of the person obviously you will have to
9:41convert that ID to nare care and that's
9:44exactly what we are doing here select ID
9:47name both the columns as usual and then
9:50I'm saying select name to the name
9:53concatenate hyphen I mean the dash and
9:56to that concatenate ID
10:00after converting it to andare Let's
10:02quickly do that okay so what we want we
10:06want select
10:10ID comma
10:12name um and I want
10:17ID sorry name to the name we want to
10:23concatenate
10:25Dash and to that we want to concatenate
10:28the ID ID from TBL employees table let's
10:33execute this and see what happens
10:35obviously we get an error conversion
10:37failed when converting the nare care
10:39value to data type integer and that
10:42makes sense you're trying to concatenate
10:44this integer to this
10:49name you know which is nare care it's
10:51not possible and you are you doing that
10:53using the plus sign okay so that's why
10:57you have to convert this to bar car you
10:59can either use cast or convert I'm using
11:02cast here cast ID as Nar
11:08care and then when we execute this
11:10obviously we'll get the output as
11:12expected and if we want to give a column
11:15name to that we use the alas as maybe
11:19name-
11:21ID so when we execute that we should get
11:23the output that we are
11:25expecting
11:28okay so we'll look at another you know
11:31real practical example and to understand
11:34this better I have this table called TBL
11:37registrations so let's look at that
11:39select star from TBL registrations table
11:43so if you check this table okay let's go
11:46back to the slides if you check this
11:47table there are users who are registered
11:51and there is this registered date so if
11:53you look at this one two you know there
11:56are two people who have registered on
11:5825th and there are 1 2 three people who
12:01are registered on 24th and there's one
12:03person who is registered on 26th now
12:05what I want you to do is I want you to
12:07write a query which will give me the
12:09total registrations by the date I don't
12:12really care at what time they have
12:14registered but I want the total count of
12:18registrations by the date okay so if you
12:21look at this I can clearly say 24th you
12:24know most registrations happened on 24th
12:273 that's the highest and least number of
12:30registrations on 26th okay so obviously
12:32if we have to do this we use Group by
12:35class again if you're new to group by um
12:37please check the earlier um you know
12:40Parts in this video series so now let us
12:43see how to write this query and
12:44obviously if you look at this here it's
12:46pretty simple in the output what you
12:48want you want the registration date and
12:50total registrations so we will select
12:52the registration date okay let's first
12:56write this
12:57query okay so in the output we want the
13:02registration date so what we need to do
13:05is we need to select the registration
13:07date so
13:09registration date and what else we want
13:13we want count
13:15of
13:17ID as let's say
13:20total from which table we want that from
13:24the registrations table so TBL
13:27registrations and we want want to group
13:31by registration date because we want to
13:34count the total number of registrations
13:41by date so we are grouping by
13:44registration date so when I execute this
13:46look at what's going to happen uh
13:48invalid column name registration date
13:50let's
13:55see so what has gone wrong there
13:57registered date not registration
14:00registered
14:16date
14:22huh okay so let's execute that now if
14:25you look at the output this is um this
14:27is not what we expected
14:29now why did we get this output because
14:32you are saying Group by registered date
14:34but within the date we have got date and
14:37time as well at the same date and time
14:40nobody has registered that's why it's
14:42not grouping anybody it's in fact
14:44grouping but there is but there is no
14:46one who is registered on the same date
14:49and same time okay so if you want to
14:53group just by the date then you'll have
14:55to convert this to date data type so
14:59let's convert this to date data type and
15:01to do that we can use cast register date
15:06as we can use
15:09the date data
15:13type so we need to do the same thing
15:16even in the select list because we don't
15:17want the time we just want the date and
15:19we want to group by that date okay so
15:22when we execute that you should get the
15:25output as expected and obviously we will
15:27not have the column name if you want the
15:29column name just give the
15:34alas registration date so obviously when
15:37we execute this we should get the output
15:40as
15:42expected okay so this query when you
15:45have casted the date to I mean the date
15:48time data type to date you are able to
15:50group them correctly as you expect by
15:53day otherwise we got the output as shown
15:56here which is incorrect which is not
15:58what we want
16:00and finally we'll talk about the
16:01differences between cast and convert
16:04okay so you know if we want to convert
16:06from one data type to another data type
16:08we have two ways why do we have two ways
16:09in the first place why do we have these
16:11two different functions
16:14Now cast is actually an NC standard okay
16:17it's based on standards so obviously if
16:20it's based on standards then that
16:22function can be used on different
16:25database
16:26applications okay so if portability is a
16:29concern and if you want to use the
16:31script that you have written with other
16:32database applications use cast because
16:35it's based on ANC standard whereas
16:37convert is specific to SQL Server okay
16:40but the advantage of using convert is
16:42that it provides more flexibility than
16:45cast and we have seen examples where uh
16:48we have actually controlled how we want
16:50datetime data types to be converted
16:52using styles with the convert function
16:55okay with the cast function we cannot
16:57use style parameter meter okay now when
17:01do you when should you use one over the
17:03other now the general rule is since cast
17:06is based on ANC standard use cast okay
17:10unless you want to take the advantage of
17:13the style functionality in convert
17:16function okay on this slide you can find
17:19resources for asp.net and C interview
17:21questions that's it for today thank you
17:23for listening have a great day

About this transcript

This page contains the full transcript of Cast and Convert functions in SQL Server Part 28 by kudvenkat, generated from the public captions YouTube serves with the video. The transcript has 2,817 words across 389 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.