SQL Tutorial - 47: Date & Time Functions (Part-3) Video Lecture | Microsoft SQL for Beginners - Database Management

hello guys welcome to my channel this is
the 47th dude oil in this course and in
this tutorial we're going to check out
some more date time functions and the
first one that we're going to have a
look at is the month name function and
this function takes in a date value as
an argument and it returns the name of
the month of the date passed right so to
use this function you have to type in
the Select keyword first and then the
name of the function which is month name
and then within parentheses you just
type in some date value within single
quotes so I just pass it in 2017 let's
say March and the fourth day right
so when we execute we see that in the
results that we get March so now you
know it's pretty obvious I mean if
you're passing in a date value you can
tell yourself you know what the month is
but then you know if you are displaying
messages let's say to users on your
website and if suppose you know some
user has entered his or her date of
birth you know while signing up on your
website and if you want to display a
message for his birthday month right in
that case you know I mean you're not
going to check or monitor for each user
and then type in a message and you know
display on the user is a profile or
something but you know instead you're
going to have all these functions
running in the background and then these
functions are going to fetch the you
know day names and the month names and
all those things and help you sort of
make a message you know a common message
for all your users and you know but with
different month values and different day
values so I hope you guys are getting my
point but then you know even if you
don't want to use these functions it's
absolutely okay you know I just just
want you to know that you can do all
these things in SQL and the next one
that we're going to check out is the
time diff function and this function is
used to find difference in time values
and there is actually a limit to which
you can obtain the difference and I
guess that's about eight hundred and
fifty yards or something for MySQL I
don't know about other distributions and
I'm not even sure whether it's different
for different
distributions but then for MySQL I guess
the limit is close to 800 RS so if the
if the difference exceeds that then it
just returns that value and nothing else
right so but if if the date or time
values are like really close then this
is a very useful function it will tell
you the exact difference in you know
number of hours number of minutes number
of seconds that kind of thing so to use
this function you have to get in select
and then the name of the function which
as I said is timed if T IME DI double F
and then you get instead of parenthesis
and you pass in time values in like
complete time values so you'll have to
mention dates too right so let's say I
want to find the difference between 1976
and then October 11th and let's say the
time value is 1750 hours and 28 seconds
and let the second value be 1976 October
11th and then 8:00 p.m. okay so that
would be 2008 and then let's say 27
seconds so when I would execute this
function I would get that you know the
difference between these two values is
-2 hours and 17 minutes and 59 seconds
so you know I'm sure you guys would
appreciate that it's a wonderful
function and you can imagine
applications of it and the next one that
we're going to check out is the time to
seconds function and you don't actually
write it that way it's time underscore
to underscore sec that's what the name
of the function is so as you can guess
what this function does is it takes in a
time value and returns the value in
number of seconds so it basically takes
in our minute and second values and it
returns the second values by multiplying
the number of hours by 3600 multiplying
the number of minutes by 60 and then
adding the second values if there is
actually a second argument right so we
use it you type in time underscore to
underscore sec and then within
parenthesis you type in a time value
within single quotes of
so let's say I want to find the second
value of 23 hours 15 minutes and 26
seconds put in a semicolon at the end
when I execute the function I get
eighty-three thousand seven hundred
twenty-six so you know I guess there are
86,400 seconds in a day so it's you know
just little short of reaching the 2400
mark right so let's take on one more
function and this is my favorite
function and it's actually a function
for all the programmers who are watching
this course it is to obtain the UNIX
timestamp of date values and if you're
working with some kind of programming
language you know like PHP or Python you
know if you're doing scripting and stuff
then I'm sure you guys must be used to
working with UNIX timestamp values and
you know it's it's basically a UNIX
timestamp is you know is the number of
seconds that have elapsed between the
time or date that you pass in and the
first January 1970 and the values in
seconds right so with that value you can
you know you know just work with dates
in a more efficient way and if you would
just like the functions that we've
discussed in this course I mean you know
the ones that we've discussed before the
one that we're going to check out now
you know you can just skip the rest of
the tutorial and you know you don't
really have to know this function that's
what I want to say so I just
demonstrated you type and select and
then you type in UNIX underscore time
stamp and you know if you do not pass in
any argument to this function then it
returns you know the time stamp for the
current time and date value right so I
will put in a semicolon at the end to
obtain like the current timestamp when I
execute it I get you know these many
seconds of elapse between first January
1970 and 15th January 2014 149 p.m.
right so that's it for now and thank you
so much for watching this tutorial I
don't think there are any more date and
time function that we're going to
discuss in this course but there are
some more important topics like views
and sub queries and you know maybe a
couple of DD
el commands like altar table or
something so we're going to check out
those in the next tutorials and you may
subscribe to my channel and I am going
to see you soon