EOMONTH function in SQL Server 2012 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 125 of sequel server
tutorial in this video well discuss evo
month function in sequel server Evo
stands for and off so this is end of
month function it's introduced in
sequence over 2012 it returns the last
day of the month of the specified date
here is the syntax the first parameter
for this function is the start date so
that is the date for which to return the
last day of the month the second
parameter is month to add this is
optional this parameter specifies the
number of months to add to the start
date so if we specify a value for the
second optional parameter then end of
month is going to add those number of
months to the start date and then it is
going to return the last day from the
resulting date let's look at an example
now so notice here we are passing this
state to end of month function and if
you look at the month here it is
November and November month has got 30
days so the last day of November 20th
2015 should be 30th of November 2015 I
have the same query already typed here
so when we execute this we should get
30th of November as the last day now
let's look at an example of retrieving
the last day from the February month
from a non-leap year 2015 is a non-leap
year and we want the last day of
February month so I'm going to specify
the month number as 2 in a non-leap year
February has 28 days so when we execute
this query we should get 20th of
February as the last day notice that we
get 28th of February as the last day now
let's look at doing the same thing from
alipio 2016 is alipio now how do we know
a specific year is in leap year if that
year is divisible by 4 then we know it's
a leap year so 2016 is divisible by 4 so
we get 504 so that's alipio with an
olympia february has got 29 days so when
we execute the same query we should get
29th of february as the last day notice
29th of
ready now let's look at an example of
using the second optional parameter
month to add when we specify a value for
this parameter it's going to add those
many number of months to the start date
and from the resulting date the end of
month is going to return us the last
eight so the moment the day that we have
is February 28 2016 now what I'm going
to do is add two months to it so when we
add two months to February we will get
April March and April okay so we want to
find the last day of April 2016 so let's
execute this in April we've got 30 days
look at that April 30th is the last day
now let's look at an example of
subtracting a month you know from the
start date to subtract a month all you
have to do is use a negative number
let's say I want to subtract one month
and then compute the end of that
resulting date so in this case it's
going to subtract one month so when we
subtract one from February we are going
to get you know January 2016 in the
month of January we have caught 31 days
so January 31st 2016 should be the last
day we have all those examples right
here now let's look at an example of
using this end of month function with
table data we'll use this employees
table for this example notice that we've
got three columns in this table ID name
and date of birth what we want to do is
return the last day of the month from
the date of birth of every employee so
if you look at mark is born on you know
January 11th 1980 Don is born on 12th of
December 1981 so from their date of
births we want the last day of the month
so for January it is 31st of January for
December this 31st of December for
November it is 30th of November so from
the date of birth of every employee we
want the
last day of that month okay so let's
look at an example of using that
end-of-month function with table data so
I'm going to use end of month and we
want to use it on date of birth column
and what I'm going to do is retrieve the
date of birth column as well and let's
give this column the last column and
aleeah's let's call this last day so
when we execute this we should get the
last day from the date of birth of every
employee okay so at the moment we are
getting the complete date
now instead of complete date if you want
just the last day as you can see here
from the month of their date of birth
then use date part function so all you
need to do here is so this end of month
is actually going to return a date so
from that date I want a part of it so
I'm going to use date part function all
I want is the date part okay I don't
want month and year so we want date part
when we execute this we should get only
the last day of the month not the
complete data as you can see it here
thank you for listening and have a great
day