YT YouTube2Text

YouTube2Text → Transcripts → DatePart, DateAdd and DateDiff functions in SQL Server Part 27

DatePart, DateAdd and DateDiff functions in SQL Server Part 27 — Transcript

by kudvenkat · 3,521 words · 485 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:02Venit this is part 27 of SQL server in
0:05this session we'll learn about the final
0:07set of datetime functions that are
0:09available in SQL Server specifically
0:11we'll be looking at date part date ad
0:13and date diff functions and finally
0:16we'll look at a practical example of
0:17using these functions before continuing
0:20with this session I strongly recommend
0:21to watch Parts 25 and 26 of this video
0:26series so before we talk about anything
0:29if you look at these three functions
0:31date part date ad and date def all of
0:34them you know have one common parameter
0:37which is nothing but the date part okay
0:40now what are the values that you can
0:42pass for this date part parameter you
0:44know those values are listed here this
0:47is the complete name and these are the
0:49abbreviations we have spoken about this
0:51date part and their abbreviations in the
0:53previous session as well okay now let's
0:56look at date part before actually
0:58looking at this definition let's look at
0:59the example and understand what this
1:01date part method trying to do now if you
1:04look at the name itself it's saying date
1:06part you want the part of a date now if
1:09you look at the date it has got several
1:10parts for example it has got year month
1:14I mean Year Day Day month hours minutes
1:18seconds Etc now it's possible that you
1:20can retrieve parts of that date using
1:23date part function okay so okay from
1:27this date the second parameter that we
1:29have from from this date I want a
1:32weekday part is it possible absolutely
1:35using date part function similarly let's
1:38say I want the month of this date which
1:41is 8 which is nothing but August okay
1:44number eight can I get that back
1:47absolutely pass in the value for date
1:50part parameter as month okay so if you
1:54look at this we have the complete date
1:56part names and the abbreviations I
1:58always prefer using the complete
2:00um names instead of the
2:02abbreviations okay so if you look at
2:04this date part you pass in the week day
2:07and then the date itself it gives you
2:10the number of the weekday in a in a week
2:13we have got seven days so Thursday is
2:15number five so it returns number five
2:19that's date part so if you look at date
2:21part definition it returns an integer so
2:24date part returns an integer
2:26representing the
2:28specified date part
2:30okay so here I'm saying I want the
2:32weekday part of this date that will be
2:35returned in the form of an integer so
2:37that's five Thursday is five so five is
2:41returned back now this date part
2:43function is very much similar to date
2:44name the only difference between date
2:46part and date name is that date part
2:49returns an integer whereas date name
2:52returns an nare care so if you want the
2:56name of the weekday instead of the
2:57integer then use date name name okay and
3:01we have an example of that
3:03here so if you look at this it's the
3:06same example that we have seen on the
3:07slide so when we execute this from that
3:11date we get the date
3:12part which returns five and date name
3:16which returns Thursday for example let's
3:19say I want the month number I can just
3:22say month and we execute that query we
3:26get the month number which is nothing
3:28but eight on the other and if you want
3:30month name you can just pass that to
3:33date name which we have seen in the
3:34previous
3:35session which is nothing but August so
3:38date part and date name are pretty much
3:41similar date add again the name itself
3:44is self-explanatory you want to add
3:47something to the date now again when you
3:49look at the date okay if you want to add
3:52something to this date you have to tell
3:54okay to which part of this date do you
3:56want to add something do you want to add
3:57it to the year month or day or hours
4:00minutes seconds milliseconds okay so you
4:03specify that using date part the first
4:07parameter okay the first parameter is
4:09the date part parameter and the second
4:11parameter is how many days or how many
4:13months or how many years do you want to
4:15add okay so so and the final one is the
4:19date
4:20itself all right so here we are saying
4:23okay to this date add 20 days so to
4:28August 30 we will add 20 20 days so
4:30which will be like September 19th
4:33because in August I think there are 31
4:35days so September 19th 2012 and the time
4:39Remains the Same okay it's possible that
4:42you can add negative values as well so
4:44when you add negative values what
4:45happens it actually subtracts those so
4:48to August 30th here we are we are
4:50actually subtracting 20 days so it's
4:52August
4:5310th okay so date add basically Returns
4:56the date time after adding specified
4:59number to add to the date part specified
5:02of the given date and if you look at the
5:04examples it's pretty much the same thing
5:07that we have seen on the slides so when
5:08we execute this we should get the same
5:11output as we have seen on the
5:13slides and finally date diff function
5:17again date diff the name itself is
5:19self-explanatory it will give you the
5:21difference between the two dates okay
5:24now if you look at this again this
5:26function also takes date part parameter
5:30okay so you're saying okay I want the
5:32difference in months between these two
5:34dates so if you look at the first date
5:36um it's November 30th 2005 and this is
5:40January 31st 2006 and you are saying
5:43okay I want the difference in months
5:46which is nothing but there is roughly
5:48two months difference between these two
5:50days okay which is nothing but December
5:52in 2005 and January in 2006 so 2 months
5:57in the second query you are saying okay
5:59between the two same dates I don't want
6:02I want the difference in
6:04days okay so entire December 31 days and
6:09entire January 31 days which is 62 days
6:12so date death so these are pretty simple
6:14datetime functions okay uh used for
6:17different purposes the one common thing
6:20to keep in mind is about these date
6:23Parts all right so with that let's look
6:27at a practical example of using these
6:29data functions now if you look at the
6:32table on the right I have the ID of the
6:33person name and date of birth now in
6:36this table I'm not storing the age of
6:38the person why it doesn't make sense to
6:40store the age while you can compute the
6:43age using the date of birth of the
6:44person so in reality nobody stores the
6:47age of the person it doesn't make
6:48because it doesn't make sense because it
6:50keeps on changing we only store the date
6:52of birth of a person and whenever we
6:54need his age we can calculate that by
6:57writing a query Okay so so I have a
7:00table like this and I want you to write
7:02a query which gives me all the columns
7:05including uh the age I think I have
7:08misspelled the column name instead of
7:10saying date of birth I should have said
7:12age so age so this Sam is 31 years old
7:17John is 27 years 0 months 10 days old so
7:20basically I need their age as well so
7:23how to do that so obviously we can use
7:25the functions that we have just
7:27discussed so if you look at the function
7:29that we have I have already written this
7:31query so that we don't have to waste
7:33time in typing and it's pretty simple to
7:36understand so if you look at this query
7:38it seems to be a little big but the only
7:41complicated piece here is this case
7:44statement so if you understand this the
7:46logic behind having this case statement
7:48here the rest of it is pretty simple so
7:51obviously if we have to compute the age
7:53of the person the simple formula that we
7:55have is from the current system date and
7:59time
8:00subtract his date of
8:02birth okay now how to get the current
8:05system date and time we can use the get
8:08date function okay and so look at this
8:12if you look at this um code here I have
8:15two variables one is uh date of birth
8:17and the other one is temp date both of
8:20type date time and I have three
8:23variables years months and days to hold
8:26their years months and days obviously
8:28they of type in
8:30and I am initializing the date of birth
8:32of the person to something you know um
8:358th October
8:371982 and what we are doing here we are
8:40assigning that date of birth to another
8:42temporary variable temp date for
8:44processing so these things are pretty
8:46simple Declaration of variable
8:48initializing to something and then
8:50assigning that value to another variable
8:52so the first thing that we have to do is
8:54to compute the number of years how many
8:57years of difference is there between the
9:00date of birth and the current date and
9:02to do that we are using date diff
9:04function and I'm saying okay I want the
9:07difference in years and I'm passing the
9:09start date which is nothing but the date
9:11of birth of the person and the current
9:14date and time using get date function so
9:16once I subtract that I get the
9:19difference in age I mean difference in
9:20years okay but I want to also calculate
9:23the difference in months and days which
9:25we'll do in a bit okay but then if you
9:27look at this after we get the difference
9:29years we are subtracting something from
9:32that okay what is that and why do we
9:34need to that understand that so let's
9:36understand the logic for doing this why
9:38do we have to do it in the first place
9:40now to do that I have written a simple
9:41query here to understand
9:44that so if you look at this you know the
9:47dates that I have here is uh November
9:4930th 2005 and January 1st uh January
9:5331st 2006 now if I say I want the
9:57difference in years between these two
10:00dates look at this I'm using date def so
10:03when I execute this query let's see what
10:05we get I get it says there is a one-ear
10:09difference between these two dates so if
10:10you look at these two dates do we really
10:12have onee difference no okay it's just
10:16you know this is November 30th which
10:19means December of 2005 and January
10:22literally 2
10:24months okay but because of the fact that
10:26it is 2005 and this is 2006 there is a
10:29difference of one de div function
10:31incorrectly calculates that okay that is
10:34the reason why we have to do this case
10:38so we are saying okay if I get one in
10:41this case it's not valid okay there's
10:44actually 0o years and 2 months
10:47difference roughly okay so that's why
10:50I'm saying okay if the month of date of
10:53birth which is nothing but the start
10:55date let's take this if the month of the
10:56date of birth is greater than the month
10:59of get date which means the current date
11:00and time
11:03or okay so if if this condition is true
11:07then what I want to do I want to
11:09subtract from this years one because why
11:14you know these are already I mean there
11:17is no onee difference between my date of
11:19birth and the current system date and
11:21time that's why we are subtracting here
11:24okay and the same logic applies if the
11:27months are equal okay so you know for
11:30some reason the months are equal but
11:32then there could be difference in days
11:34that's why we are saying okay if the day
11:37number of the date of birth is greater
11:39than the day number of the current
11:40system date and time we still want to
11:42get that one and subtract from that year
11:45otherwise don't subtract anything zero
11:48so it remains one year and then next
11:52what we need to do we need to add that
11:55year to the date these years whatever we
11:59get that we need to add to the year uh
12:02to the date of birth otherwise what will
12:04happen look at this if I say okay I
12:07don't want the year difference uh let's
12:09say I want the difference in days for
12:13example okay so if I execute
12:18this it gives me 62 days why because um
12:24you know it takes the if you look at 30
12:26and 31 it's only one day but it takes
12:28the month also into
12:30consideration the month's also into
12:32consideration that's why it will give us
12:3462 31 days in the month of December of
12:372005 and 31 days in January 2006 the
12:41same is applicable to years as well
12:43that's why to the um you know actual
12:47date of birth since you have calculated
12:48the number of years you don't want that
12:50to be carried forward into the into
12:53calculating months what we do we add
12:55those years to the temporary date of
12:58birth and then we compute the months so
13:01we only get the months now okay so once
13:04we get those months and it's the same
13:05logic here again okay date def I want
13:08months from that temp date and the
13:11current date okay the difference between
13:14these two in terms of months okay and
13:17this logic is the same the reason why we
13:20did this case statement here is the same
13:22here as well okay and finally what we I
13:26mean and next we'll actually add the
13:28months to the temp date and finally we
13:32calculate the difference in days between
13:34the date of birth and the current time
13:37and what we are doing here okay since we
13:39have calculated months days and years on
13:43the top we finally select that select
13:46years as years months as months and days
13:48as days okay so if we execute this query
13:52let's comment out this line
13:55here so if I execute this query you
13:58should see for a person born on 8th
14:00October 1982 his age is 29 years 10
14:03months and 24
14:05days okay now okay we have done this but
14:09what is our ultimate aim our ultimate
14:11aim is basically to achieve an output
14:14like this now to achieve an output like
14:16this we have to convert this into a
14:19function we haven't spoken about writing
14:21user defined functions yet which we'll
14:23be doing in a later session but for
14:26completion sake let me try to convert
14:28this into a function
14:29okay here we are hardcoding the value we
14:31don't want to be hardcoding the value we
14:33want to be passing this as a parameter
14:35to a function okay so we have seen how
14:37to create stored procedures to create
14:39stored procedures we use create
14:41procedures you know and then the stored
14:43procedure name similarly to create a
14:44function we will say create function and
14:47the function name so
14:50create function and then the function
14:52name let's called FN uh let's say
14:56compute H okay okay so obviously if you
15:00want to compute age of a person to that
15:02function you'll have to pass a parameter
15:04so let's say I want to pass date of
15:08birth let's say date time is the
15:12parameter and this function is going to
15:16return an nare care of maybe uh what do
15:21you say
15:2350
15:26s begin
15:29and what we want to do is end
15:32this okay so what we are saying we are
15:35saying okay this function is going to
15:37take date of birth as the input
15:39parameter and return his age in the form
15:43of nare okay since we are passing in the
15:46parameter we don't have to have this
15:48hardcoded value here so we can get rid
15:51of
15:53that okay so that's the first change and
15:56the final change that we have to do is
16:00instead of selecting these as three
16:02different columns let's select them
16:04let's assign them to a variable of type
16:06nare and return back from the function
16:09okay to do that first we need to declare
16:12a variable of type NW care so let's call
16:15this at age of type nare care of let's
16:19say
16:2150 and we are saying to this
16:25variable okay if you look at the output
16:27what we want we want the years and then
16:3031 years 8 months 2 days old which means
16:36we have this years months and days we
16:38need to concatenate to those numbers you
16:41know these strings years months and days
16:43old okay and to do that we will
16:46basically say set at age is equal
16:51to we want to assign to that age at
16:54years plus and we will say because this
16:57variable contains the total number of
16:59years and plus what else we want we want
17:02the months as well so copy the months
17:05variable and then to that say that many
17:10months and
17:12finally append that to
17:17days and
17:19say day
17:22sold that's it so we can get rid of the
17:25select statement
17:27finally and then we will return the age
17:30of the person so if you look at this age
17:34variable it's of type nare care of 50
17:37and if you look at our function this
17:39function returns an nare of 50 that's it
17:42okay but there is a small thing that we
17:44have to do if you look at this years
17:46years data type is an integer similarly
17:50months and days as well so but we are
17:53concatenating an integer
17:56variable to nare car
17:59you know string so in order to do that
18:02you will have to convert that to nare
18:04care and to convert one data type to
18:06another data type we can use the cast
18:09function cast
18:11years
18:13as and we care of maybe
18:18four okay let's copy that we have to do
18:21the same thing for months
18:25and days so cast month
18:29months as NW care months are usually two
18:33so let's put that similarly we have to
18:36do it for days so
18:40cast days as nare care of two that's
18:46said we have our function
18:49now so let's execute create this
18:51function so command completed
18:53successfully which means this function
18:55has got created okay now to execute that
18:58function
18:59just like any other system function
19:01you'll say select
19:03FN compute Ag and to this function
19:06you'll have to pass a date of birth so
19:09let's say let's pass in this date of
19:16birth and when you execute this you'll
19:18get an error okay so for this user
19:23defined function you'll have to prefix
19:25dbo because it's present in that dbo
19:28scheme schema we'll talk about functions
19:29in detail in a later session don't
19:32worry so when we press that I get 6
19:35years 9 months 2 days so look at this I
19:37got this as a one column now now what I
19:39can do now is basically use this
19:43function within a select statement so
19:45when I say select star from TBL employee
19:48table we get all those columns it's
19:51actually employees column which is
19:53called the date of
19:54birth okay so what we want now along
19:58with the date of birth we need the age
20:00of the person okay so if we want to age
20:03if we want the age of the person we can
20:05use this function okay so from this we
20:08want the ID of the person name of the
20:11person as usual um date of birth as
20:15usual but then we want the age of the
20:19person so we use this function which is
20:21capable of calculating the age and we
20:24will give it an alias name as age from
20:27TBL employee so let's execute this and
20:30see what's going to happen Okay so age
20:33column but if you look at this for
20:34everybody it is saying 6 years 9 months
20:362 days old and you should have guessed
20:38why that's because we have passed this
20:41date you know hardcoded date here
20:44instead of that we want to be passing
20:46the column name itself so for each row
20:50this function calculates their age and
20:52prints that here so when we execute this
20:55we should get their ages so now this
20:59matches what we want the age of the
21:02person now here this logic don't worry
21:04about this logic um you know um if it is
21:07too complicated for you at this time I
21:10will go ahead and paste this in my blog
21:13check my blog it should be there in case
21:15if you want to digest that later I hope
21:17you have enjoyed the
21:19session on this slide you can find
21:22resources for asp.net and cop interview
21:24questions that's it for today thank you
21:26for listening have a great day

About this transcript

This page contains the full transcript of DatePart, DateAdd and DateDiff functions in SQL Server Part 27 by kudvenkat, generated from the public captions YouTube serves with the video. The transcript has 3,521 words across 485 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.