DatePart; DateAdd and DateDiff functions in SQL Server Part 27 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to presume technologies I
am venket this is part 27 of sequel
server in this session we'll learn about
the final set of daytime functions that
are available in sequel server
specifically we'll be looking at date
part data and data functions and finally
we'll look at a practical example of
using these functions before continuing
with this session I strongly recommend
to watch parts 25 and 26 of this video
series so before we talk about anything
if you look at these three functions
date path data ad and they diff all of
them you know have one common parameter
which is nothing but the date part okay
now what are the values that you can
pass for the state part parameter you
know those values are listed here this
is the complete name and these are the
abbreviations we are spoken about this
date part and their abbreviations in the
previous session as well
okay now let's look at that part before
actually looking at this definition
let's look at the example and understand
what this date part method trying to do
now if you look at the name itself it's
saying date part you want the part of a
date and if you look at the data it has
got several parts for example it's got
year month I mean your day day month
average minutes seconds etc now it's
possible that you can retrieve parts of
that date using date part function okay
so okay from this date the second
parameter that we have from this date I
want a week date part is it possible
absolutely using date part function
similarly let's say I want the month of
this date which is 8 which is nothing
but August okay number 8 can I get that
back absolutely pass in the value for
date part parameter as month okay so if
you look at this we have the complete
date part names and the abbreviations I
always prefer using the complete names
instead of the abbreviations okay so if
you look at this date part you pass in
the weekday and then the date itself it
gives you the number of DB day in a week
we have got
and days so Thursday is number five so
it returns number five
that's date part so if you look at date
part definition it returns an integer so
date part returns an integer
representing the specified date part
okay
so here I'm saying I want the weekday
part of this date that will be returned
in the form of an integer so that's five
Thursday is five so five is returned
back now this date part function is very
much similar to date name the only
difference between date part and date
name is that date part returns an
integer
whereas date name returns an n-well care
so if you want the name of the weekday
instead of the integer then use date
name okay and we have an example of that
here so if you look at this it's the
same example that we have seen on the
slide so when we execute this from that
date we get the date part which returns
five and date name which returns
Thursday for example let's say I want
the month number or I can just say month
and we execute that query we get the
month number which is nothing but eight
on the other hand if you want month name
you can just pass that to date name
which we have seen in the previous
session which is nothing but August so
date part and date name are pretty much
similar data add again the name itself
is self-explanatory you want to add
something to the date now again when you
look at the date okay if you want to add
something to this date you have to tell
okay to which part of this date do you
want to add something do you want to add
it to the year month or day or hours
minutes seconds milliseconds okay so you
specify that using date part the first
parameter okay the first parameter is
the date part parameter and the second
parameter is how many days or how many
months or how many years do you want to
add okay so so and the final one is the
date itself all right so here we are
saying okay
to this date add 20 days
so - August 30 we will add 20 days so
which will be like September 19th
because in August I think there are 31
days so September 19 2012 and the time
remains the same ok it's possible that
you can add negative values as well so
when you add negative values what
happens it actually subtracts those so -
August 30th here we are we are actually
subtracting 20 days so it's August 10th
ok so date add basically returns the
date time after adding specified number
to add to the date part specified of the
given date and if you look at the
examples it's pretty much the same thing
that we have seen on the slide so when
we execute this we should get the same
output as we have seen on the slides and
finally date diff function again day to
day if the name itself is
self-explanatory it will give you the
difference between the two dates okay
now if you look at this again this
function also takes date pad parameter
okay so you're saying okay I want the
difference in months between these two
dates so if you look at the first date
it's November 30 at 2005 and this is
January 31st 2006 and you are saying
okay I want the difference in month
which is nothing but there is roughly
two months difference between these two
days which is nothing but December in
2005 and January in 2006 so two months
in the second query you are saying okay
between the two same dates I don't want
I want the difference in days okay so
entire December 31 days and entire
January 31 days which is 62 days so date
death so these are pretty simple date
time functions okay are used for
different purposes the one common thing
to keep in mind is about these date
parts alright so with that let's look at
a practical example of using these day
time functions now if you look at the
table on the right I have the idea of
the person name and date of birth now in
this table I am NOT storing the age of
the person why it doesn't make sense to
store the Aged well you can compute the
age using the date of birth of the
person so in reality nobody stores the
age of the person who doesn't make
because it doesn't make sense because it
keeps on changing we only store the date
of birth of a person and whenever we
need his age we can calculate that by
writing a query ok so I have a table
like this and I want you to write a
query which gives me all the columns
including the age I think I have
misspelled the column name instead of
saying date of birth I should have said
8 so age so this Sam is 31 years old
John is 27 years 0 months 10 days old so
basically I need their age as well so
how to do that so obviously we can use
the functions that we have just
discussed so if you look at the
functions that we have I have already
written this query so that we don't have
to waste time and typing and it's pretty
simple to understand so if you look at
this query it seems to be a little big
but the only complicated piece here is
this case statement so if you understand
this the logic behind having this case
statement here the rest of it is pretty
simple so obviously if we have to
compute the age of the person the simple
formula that we have is from the current
system date and time
subtract his date of birth ok now how to
get the current system date and time
we can use the get date function okay
and so look at this if you look at this
code here I have two variables one is
date of birth and the other one is temp
date both of type date time and I have
three variables years months and days to
hold their ears months and days
obviously they're of type integer and I
am initializing the date of birth of the
person to something you know 8 October
1982 and what we are doing here we are
assigning that date of birth to another
temporary variable temp date for
processing so these things are pretty
simple declaration of variable
initializing to something and then
assigning that value to another variable
so the first thing that we have to do is
to compute the number of years how many
years of difference is there between the
date of birth and the current date and
to do that we are using date diff
function and I'm saying okay I want the
difference in years and I'm passing the
start date which is nothing but they
date of birth of the person and the
current date and time using get date
function so once I subtract that I get
the difference in age I'm in difference
in years
okay but I want to also calculate the
difference in months and days which
we'll do in a bit okay
but then if you look at this after we
get the difference in years we are
subtracting something from that okay
what is that and why do we need to that
understand that so let's understand the
logic for doing this why do we have to
do it in the first place
now to do that I have written a simple
query here to understand that so if you
look at this you know the dates that I
have here is number 30 at 2005 and
January 1st January 31st 2006 now if I
say I want the difference in years
between these two dates look at this I'm
using date death so when I execute this
query let's see what we get
I get it says there is a one-year
difference between these two dates so if
you look at these two dates do we really
have one year difference no okay it's
just you know this is number two third
year which means December of 2005 and
January it literally two months okay but
because of the fact that it is 2005 and
this is 2006 there is a difference of
one did they function in correctly
calculates that okay that is the reason
why we have to do this case so we are
saying okay if I get one in this case
it's not valid okay
there's actually zero years and two
months difference roughly okay
so that's why I'm saying okay if the
month of date of birth which is nothing
but the started let's take this the
month of the date of birth is greater
than the month of cad date which means
the current date and time or okay so if
if this condition is true then what I
want to do
I want to subtract from this year's one
because y-you know these are already I
mean there is no one-year difference
between my date of birth and the current
system date and time
that's why we are subtracting here okay
and the same logic applies if the months
are equal okay so I know for some reason
the months are equal but then there
could be difference in days that's why
we are saying okay if the day number of
the date of birth is greater than the
day number of the current system date
and time
we still want to get that one and
subtract from that year otherwise don't
subtract anything zero so it reminds one
year and then next what we need to do we
need to add that year to the date these
years whatever we get that we need to
add to the year to the date of birth
otherwise what will happen look at this
if I say okay I don't want the ear
difference let's say I want the
difference in days for example okay so
if I execute this it gives me sixty-two
days why because you know it takes them
if you look at 30 and 31 it's only one
day but it takes the month also into
consideration the months also into
consideration that's why it will give us
60 - 31 days in the month of December of
2005 and 31 days in January 2006 the
same is applicable to ears as well
that's why do the you know actual date
of birth since you have calculated the
number of years you don't want that to
be carried forward into the into
calculating months what we do we add
those ears to the temporary date of
birth and then we compute the months so
we only get the months now okay so once
we get those months and it's the same
logic here again okay date if I want
months from that temp date and the
current date okay the difference between
these two in terms of months okay and
this logic is the same the reason why we
did this case statement here is
same here as well okay and finally what
we I mean and next will actually add the
months to the temp date and finally we
calculate the difference in days between
the date of birth and the current time
and what we are doing here okay since we
have calculated months days and ears on
the top we finally select that select
years as years months as months and days
as days okay so if we execute this query
let's comment out this line here so if I
execute this query you should see for a
person born on 8 October 1982 his age is
29 years 10 months and 24 days ok now ok
we have done this but what is our
ultimate aim our ultimate aim is
basically to achieve an output like this
now to achieve an output like this we
have to convert this into a function we
haven't spoken about writing
user-defined functions yet which we'll
be doing in a later session but for
completions sake let me try to convert
this into a function okay here we are a
hard-coding the value we don't want to
be hard coding the value we want to be
passing this as a parameter to a
function okay so we have seen how to
create stored procedures to create
stored procedures we use create
procedures you know and then the stored
procedure name similarly to create a
function we will say create function and
the function name so create function and
then the function name let's call FN
let's say compute H ok so obviously if
you want to compute age of a person to
that function you will have to pass a
parameter so let's say I want to pass
date of birth let's say date time is the
parameter and this function is going to
return an n where care of maybe what you
say 50s
begin and what we want to do is and this
okay
so what we are saying we are saying okay
this function is going to take date of
birth as the input parameter and return
his age in the form of n well care okay
since we are passing in the parameter we
don't have to have this hard-coded value
here so we can get rid of that okay so
that's the first change and the final
change that we have to do is instead of
selecting these as three different
columns
let's select them let's assign them to
your variable of type and we're cat and
return back from the function okay to do
that first we need to declare a variable
of type and we're care so let's call
this at age of type and we're care of
let's say 50 and we are saying to this
variable okay if you look at the output
what we want we want the ears and then
31 years eight months two days old which
means we have this years months and days
we need to concatenate to those numbers
you know these strings years months and
days old okay and to do that we will
basically say set at age is equal to we
want to assign to that age at years plus
and we will say because this variable
contains the total number of years and
plus what else we want we want the
months as well so copy the months
variable and then to that say that many
months and finally append that to days
and say days old that's it so we can get
rid of the Select statement finally and
then we will return the age of the
person so if you look at this age
variable it's of type and we're care of
50 and if you look at our function
this function returns in and we're care
of 50 that's it okay but there is a
small thing that we have to do if you
look at this year's year's data type is
an integer
similarly months and days as well so but
we are concatenated an integer variable
to n where cap you know string so in
order to do that you will have to
convert that to n where cap and to
convert one data type to another data
type we can use the cast function cast
ears as and where care of maybe four
okay
let's copy that we have to do the same
thing for months in days so cast months
as n well care months are usually two so
let's put that similarly we have to do
it for days so cast days as n well care
of two that's it we have a function now
so let's execute create this function so
command completed successfully which
means this function has got created okay
now to execute that function just like
any other system function you will say
select FN compute age and to this
function you will have to pass a date of
birth so let's say let's pass in this
date of birth and when you execute this
you will get an error okay so for this
user-defined function you have to prefix
dbo because it's present in that dbo
schema we'll talk about functions in
detail in a later session don't worry so
when we plus that I get six years nine
months two days oh look at this I got
this as a one column now now what I can
do now is basically use this function
within a select statement so when I say
select star from TBL employee table we
get all those columns it's actually
employees call
which has caught the date of birth okay
so what we want now along with the date
of birth
we need the age of the person okay so if
we want to age if we want the age of the
person we can use this function okay so
from this we want the ID of the person
name of the person as usual date of
birth as usual but then we want the age
of the person so we use this function
which is capable of calculating the age
and we will give it an earliest name as
age from TBL employees so let's execute
this and see what's going to happen okay
so age column but if you look at this
for everybody it is saying six years
nine months two days old and it should
have cast why that's because we have
passed the state you know hard-coded
date here instead of that we want to be
passing the column name itself so for
each row this function calculates their
age and prints that here so when we
execute this we should get their ages so
now this matches what we want the age of
the person now here this logic don't
worry about this logic you know if it is
too complicated for you at this time I
will go ahead and paste this in my blog
check my blog it should be there in case
if you want to digest that later I hope
you have enjoyed the session on this
slide you can find resources face we
don't have it's a show up interview
questions so that's it for today thank
you for listening have a great day