IsDate; Day; Month; Year and DateName DateTime functions in SQL Server Part 26 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

well welcome to presume technologies I
am venket this is part 26 of signal
server in this session we'll learn about
a few more date and time functions that
are available specifically we'll be
talking about East 8 they month year and
date name functions and finally we'll
look at a practical example of using all
these functions together before
continuing with the session I strongly
recommend to watch part 25 of this video
series now in the previous part we have
seen the different date time data types
that are available in sequel server
depending on the requirement of our
project we choose the date time data
type that's most appropriate for example
if I want to store just the date part
then I use the date data dye on the
other hand if I want to store the date
the time including the offset
information then I will choose the time
offset okay so basically these are the
different data types that are available
in signals over look at this there is if
you look at the data types here we have
one called date time and the other one
date time - and if you look at the
difference between these two in terms of
seconds for date time - we have more
precision okay and we have also spoken
about various other functions that are
available in sequel server to retrieve
the current date and time okay and we
also have spoken about the UTC date and
time concepts and timezone offset
concepts in the previous session
destroy' strongly recommend to watch
part 25 of this video series before
continuing with the session in this
session we'll talk about ease date
function so ease date function basically
checks if the given value is a valid
date time or date time this function
returns one for success and zero for
failure okay so if you look at the way
we are using this function e state
you're passing in something to this
function in this case we are passing in
pragya within single quotes and look at
this can we convert this play gene into
a valid date time or date time we cannot
that's why this function returns zero
indicating that this is not a valid date
time okay but on the other hand if you
look at the second example this function
is taking in another function ket date
and we know that get date function
returns the current date and time okay
so as get it returns a valid date and
time we are passing it to e state
function to check okay is it a valid
date time obviously it's a valid date
time so the function is returning one
here okay and this is a hard-coded date
and time value you have your month day
and then hours minutes seconds and
milliseconds which is a valid date and
time so the e state function is
returning one but in the other hand look
at the fourth example that we have here
it's a valid date as well the only
difference between the third and the
fourth date is that with a fourth date
we have got more fractional seconds
precision here okay but then is date
function returns zero and if you look at
the note here for data type for
date/time values each date returns a
zero
okay and if you remember from the
previous part anything that has got you
know the long precision for the seconds
it's a date/time to data type okay this
is date time offset here also we have
the long precision okay so for these
dates each date returns false so if you
look at this example here okay is date
if we execute this this returns zero
okay so keep that in mind each date
function returns zero for day time two
values okay that's about a state
function and these are the examples so
obviously if we execute all of them
together so ease date for presume
returns zero get the it returns the
current date and time it's a valid date
so it returns one and this is a valid
date and time so that returns one the
third one and finally this is a date
time to data type date so that's why e
State returns zero
for that pretty simple to understand
okay so next we will talk about day
month and year functions these are very
very simple to understand
obviously the day function returns the
day number of the month of the given
date so for example if you look at the
examples here I have this get date
function so obviously get date returns
the current date and time so if you look
at the current date and time on my
machine it is you know basically first
of September 2012 so the day will return
one here because get date returns
September 1st 2012 and if you look at
this here they where we have the
hard-coded date the date here is 31st of
January 2012 so the day function returns
31 and look at the month it returns the
month number of the year so obviously
today is September 1st 2012
so month of get date returns you know
number 9 for September and here this is
January 31st 2012 so month function
returns 1 which is very straightforward
and along the same lines iya function
returns the e'en number of the given
date so get date returns the current
system date and time
so September 1st 2012 so year part is 12
I mean 2012 so this returns 2012 whereas
the second one also returns 2012 because
the year here is 2012 so basically
depending on which part of the date you
want you can choose either day month or
year functions let's look at them
practically real quick so obviously
select get get date returns the current
date and time so they should return us 9
so that returns 9 and they should return
that you want because 31 is the day and
along the same lines
today is September so when we execute
this this the first method call should
return 9 and here the value with the
month value is hard-coded to first month
so it returns 1 and if you look at the
year function
obviously this is 2012 September 1st
2012 so the first one returns 2012 and
the second one returns 2012 as well
because we have it hard-coded here all
right so that's about day month and year
functions date name function this is a
very very useful function okay so date
name function basically returns a string
okay let's quickly prove that so if you
look at this here select date name so if
you look at the function itself sorry
about that okay
so select date night date name if you
look at the function itself this
function returns an N word care a string
okay now basically there are two
parameters that this function expects
the first parameter is like which part
of the day do you want I mean which part
of the date do you want in string
representation do you want the day or
the weekday or demand okay now look at
this I'm saying if you look at the
second example specifically I'm calling
date name function and I'm passing it
weekday you know as the argument for the
first parameter which is interval
weekday and then I'm okay in this given
date tell me for the 30th of September
2012
what is the weekday is that a Sunday
Monday Tuesday Wednesday okay so if you
won't figure that out you can use date
name function so when I execute this one
it returns a Sunday similarly if you
want to find the name of the month then
you can pass month name for the interval
so basically if you look at this date
name function it expects two parameters
okay
the second parameter is the date upon
which this function will operate and the
first parameter you know you use that
parameter to tell which part of the date
do you want
okay now this date part you know look at
this the table here for example if I
want weekday I can use weekday or I can
just say DW now is it mandatory to
remember all these date parts and
abbreviations not really okay because if
you just search MSDN you get it okay
but then these are very easy to remember
as well for example if you want the ear
part you will just specify year if you
want the month you will say month if you
want the day you will say day if you
want the weekday you will say weekday
they will give you an integer number I
mean though this number is represented
in a string format it's a number it's a
day number
okay whereas if you want the weekday
named baby we use weekday similarly you
can you have several other date parts as
well for example seconds milliseconds
microseconds etc so depending on your
requirement you choose the appropriate
date path and I suggest to use the full
date part rather than the abbreviation
because that will be clear you know when
you look at that it's more readable
rather than saying dy or QQ okay so
that's about date name now let's get a
very simple example of using this date
name function okay now in our database
we have a very simple table select star
from TBL employees so if you look at
this table it has gone just you know
three columns Heidi name and the date of
birth okay just like this ID name and
date of birth now what I want to do is
we want to write a query which returns
an output something like this I want the
name of the person date of birth and the
day they are born on okay are they born
on a Monday Tuesday Wednesday Thursday
so depending on the date of birth we'll
have to calculate that and I also want
the month number is it twelve month nine
month eight month whatever and the month
name is a December August whatever and
basically the year and obviously you
would have guessed it by now how to
write this query we use the functions
that we have just discussed and if you
look at the query it's pretty simple we
want the name as it is so we will say
name and then the date of birth as it is
we'll say date of birth so that's pretty
simple
next we want the day okay the weekday is
it a Monday Tuesday Wednesday so I can
use the date name a function pass
weekday for the date part parameter and
date of birth column work on date of
birth from the date of birth find out
what is the weekday and return that has
day okay and then next we want the month
number okay if you want the month number
in the previous session I mean the
previous slide we have seen how to use
the man
function that will give you the month
number okay so we are basically using
that month function and we are telling
okay operate on date of birth column and
return the month number and similarly I
want the month name next so I am passing
in month as the date part for the date
name function and operate on date of
birth and return the month name and
finally I want the year so we are using
the ear function which is pretty simple
and we have that exact same query
written here so if you look at that this
is exactly the query that we have and
when we execute this query you should
see the output that we want on this
slide you can find resources for asp.net
and C sharp interview questions that's
it for today thank you for listening
have a great day